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.
VIDEO DEMO
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.
To summarize,
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.