Recruitment Manager Excel Template – Most Frequently Asked Question
In this blog post, we will cover the most frequently asked question about Recruitment Manager Excel template.
To learn more about the template’s features, please visit the product page.
To learn how to use the template, please visit the support page.
Before we begin, we are assuming that the template’s formulas have not been edited/modified.
Why does the Dashboard not reflect data entered?
This is by far the most common question.
The answer is somewhat long, as we need to cover various scenarios which could lead to this. Let’s explain the different reasons and how to address each of them.
1. Data Not Refreshed
The template uses pivot tables and hence we should refresh the calculations whenever we make any changes to existing data or add new data.
If you have entered data for the first time in Jobs and Applications sheets, but see that the Dashboard is completely blank, this could be one of the reasons.
Solution: Refresh the calculations. In the DATA ribbon press Refresh All button.
2. Errors in applications Data
Applications which have data entry errors will not be included in Dashboard and hence if you have any errors, that could be the reason why you don’t see what you expect in the Dashboard. We put these data validations in place so that the metrics calculated in the Dashboard are always correct.
To identify if this is true in your case, please see ERROR column (column Q in v2 of the template) in Applications sheet.
This is a calculated column. So, please don’t edit it. But you can filter using this column to see the applications having Errors.
There are two main reasons why there would be an Error.
- Required Dates are missing.
- Dates are not chronological
Let’s use a sample data set to illustrate this clearly.
For Hired Applications, all the dates until the hiring stage should have a date. In other words, applications should go through and complete all the stages to be hired.
In the above screenshot, first application is missing OFFER DT. The application status is ‘HIRED’. Hence we need to have all the stage dates until the Hire stage.
The second application has all the dates, however, the MGR INTERVIEW DT is Feb 22nd whereas the previous stage PHONE SCREEN DT is Feb 24th. That is an error, as it is assumed that the stages are done in sequence.
Job Posted Date <= Stage 1 DT <= Stage 2 DT < = Stage 3 DT <= Stage 4 DT <= Stage 5 DT <= Stage 6 DT
Note: Job Posted Date is coming from the Jobs sheet for the specific Job ID. You can also see the Job Posted Date in column Y in Applications sheet.
The third application in the screenshot does not have an error, as the dates are present and they are chronological.
It is a little different for not hired applications. This includes applications with status ‘NOT HIRED’ and applications with no status (where we have not taken a decision yet).
The dates must be in chronological order. However, we don’t need all the dates to be populated. If an application goes to Stage 3 and then is put in NOT HIRED status, then we don’t need dates for Stages 4 to 6.
In the screenshot above, the first application is missing PHONE SCREEN DT. As it has an APPLICATION DT and MGR INTERVIEW DT, it should also have the PHONE SCREEN DT in-between.
Second application has a MGR INTERVIEW DT (25th Apr) that is prior to the PHONE SCREEN DT (4th May). That is an error.
What if a candidate skips a stage?
Sometimes a candidate may skip a stage and go to the next stage directly. In such cases, it is recommended that we enter the same date and not leave it blank.
For example, if a candidate skips Phone Screen and directly goes to the Mgr Interview because it is an internal candidate and a Phone Screen may be considered unnecessary. In that case, I would enter the Application Date again as the Phone Screen Date.
This will result in the calculations of time taken for Phone screening as 0 days.
Solution: Enter missing dates and ensure they are chronological
3. Required fields are missing
In the Jobs sheet, required fields are Job ID, Job Posted Date, Positions and Status.
Each Job has to be in one of the three status values (OPEN, COMPLETED or CANCELLED).
In the Applications sheet, JOB ID and APPLICATION DT are required fields.
Required fields are marked as shown above in the template for your identification.
Solution: Enter any missing values in required fields.
4. Dates in invalid format
Please check that the dates are in correct date format. In certain language and region versions of Excel, the date formats are different from the U.S. version. This could cause Excel to not recognize the entries as dates and thus result in errors in ERROR column.
To check if the date is a valid date format, please click on the cell with the specific value. Then, click on the Number format drop down list.
If Excel shows all the formats with the same value, it means that Excel is treating it as text and not date.
If Excel treats as date, it would appear as shown below.
Solution: Enter dates in correct format.
5. Filters applied in Dashboard
If you have chosen any filters (slicers) in the Dashboard sheet, that will narrow down the data set used to calculate metrics on Dashboard. If you believe that the Dashboard is not reflecting all the jobs and applications, please check if there are any filters applied. If so, clear the filters.
Solution: Clear filters applied.
6. Open positions with 0 applications
Template uses data in Applications sheet to build dashboard. Jobs data is also pulled over to Applications sheet.
If we have 0 applications for a job, then they will not included in the Open Positions metric. It will be shown separately next to it as ‘Open Positions w/o Applications in Market’
Solution: Check if there are applications in Applications sheet for the job. Check the Open Positions w/o Applications metric.
7. No Hired/Relevant Applications
Some parts of dashboard may not be populated if there are no relevant data yet. Each section of the Dashboard calculates certain metric that needs relevant data to make that metric meaningful. If that relevant data is not present, the section will appear blank.The following shows the data that is relevant to each section on the dashboard.
- 4 KPIs related to Hired Positions – only if there are HIRED applications.
- 3 KPIs related to Open Positions – even if there are no HIRED applications.
- Recruitment Funnel – if there are applications with HIRED status or NOT HIRED status – basically applications where decisions have been taken.
- Monthly Metrics – only if there are HIRED applications.
- Pipeline Efficiency of Hiring – only if there are HIRED applications.
- Application Sources – only if there are HIRED applications.
- Decline Reasons – only if there are NOT HIRED applications.
- Cost – if cost is entered in Cost column in Jobs sheet.
- Active Pipeline – if there are applications with no status – basically applications where decisions have not been taken.
Solution: Ensure that there is relevant data in the Jobs and Applications sheet.
If you followed the above suggestions and still have any questions, please post them below in the comments section. Thanks for your support.
Hi I have 58 applications for a job but the Job Report stops at 25 applicants. Can this show all applicants?
Thanks for purchasing.
You can unlock the sheet, extend the formulas from the 25th applicant row down further to show more applicants. If you need help, please email us at firstname.lastname@example.org and we can help make this change for your file.
Thanks & Best wishes.
What does the number in the middle of the Pipeline Efficiency of Hiring means?
Tank you for showing interest in our template.
The number in the middle of the pipeline efficiency chart is Average Days to hire for all stages.
I purchased the recruitment manager spreadsheet in May 2020 and the formula has been impacted. Are you able to assist with this as soon as possible?
I would love some assistance.
I can be contacted on 02 49 50 22 69.
Our support team will respond to your email sent to email@example.com shortly.
Resource Capacity Planner template:
The Capacity Vz Demand dashboard does not show data for projects or resources. I did several refreshes after entering data in the Capacity and Demand sheets, but no data is shown on dashboards. I have followed all the steps in the guideline and other resources listed in this support section. Is it possible for me to upload my file for you to take a look at it?
We have received your file through email. Please allow us some time to check the issues and revert.
In the Applications sheet under the Status tab, there is only options of Hire or Not Hired. This looks a little silly especially when we view everyone in the Jobs Report under Applications. The candidates that I am waiting to hear feedback from the hiring manager on initial resume review have no status and really do not want to use NOT HIRED. Is there a way that I can add other options? I want to add ‘In Progress’ or ‘Manager Review’.
Also, I would like to be able to view everyone in every stage on the jobs report tab. Currently, we can only view people that are on the same stages. How can I modify?
Application Status can be blank and it indicates that no decision has been taken and will be included in the Pending applications in pipeline in Dashboard.
Stage represents the most recent stage the application was.
Formulas have to be changed in the Job Report sheet to show all applications.
Please let us know if there are any questions.
Thanks & Best wishes.
what if I change the name of one column in recruiting stage, or maybe remove it. will this effect the template overall.
Recruiting stages can be renamed in the Settings sheet, not in Applications sheet. If you don’t need a stage, you can leave the name blank in the Settings sheet.
Please do not remove any columns. You can hide them instead.
Please let us know if there are any questions.