Site icon INDZARA

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.

7 Reasons for Why Dashboard does not reflect data


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.

Data Ribbon — Refresh All

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.

Error column 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.

  1. Required Dates are missing.
  2. 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.

Errors in Hired Applications

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.

Errors in Not Hired Applications

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.

Job ID required in Jobs sheet
Required Fields in Jobs sheet

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 in Applications sheet

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.

Correct Date format check – Example Invalid Date

If Excel treats as date, it would appear as shown below.

Correct Date format check

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.

Clear Filters in Dashboard

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’

Open Positions with 0 Applications

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.

HR Dashboard Summary Metrics
Open Positions with 0 Applications
Recruitment Funnel (up to 6 stages)
Monthly Metrics (Hired and Days to Hire)
Pipeline Efficiency – Time Spent in each stage
Application Sources – Hired & Conversion Rate
Decline Reasons for Candidates/Applications
Active Recruiting Pipeline – Stage breakdown

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.


Exit mobile version