Recruitment Manager Excel Template – Most Frequently Asked Question

Posted on

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
7 Reasons for Why Dashboard does not reflect data

 

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.

Data Ribbon -- Refresh All
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
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
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
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
Job ID required in Jobs sheet

 

Required Fields 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 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
Correct Date format check – Example Invalid Date

 

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

Correct Date format check
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
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
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.

 

  • 4 KPIs related to Hired Positions – only if there are HIRED applications.
HR Dashboard Summary Metrics
HR Dashboard Summary Metrics

 

  • 3 KPIs related to Open Positions – even if there are no HIRED applications.
Open Positions with 0 Applications
Open Positions with 0 Applications

 

  • Recruitment Funnel – if there are applications with HIRED status or NOT HIRED status – basically applications where decisions have been taken.
Recruitment Funnel (up to 6 stages)
Recruitment Funnel (up to 6 stages)

 

  • Monthly Metrics – only if there are HIRED applications.
Monthly Metrics (Hired and Days to Hire)
Monthly Metrics (Hired and Days to Hire)

 

  • Pipeline Efficiency of Hiring – only if there are HIRED applications.
Pipeline Efficiency - Time Spent in each stage
Pipeline Efficiency – Time Spent in each stage

 

  • Application Sources – only if there are HIRED applications.
Application Sources - Hired & Conversion Rate
Application Sources – Hired & Conversion Rate

 

  • Decline Reasons – only if there are NOT HIRED applications.
Decline Reasons for Candidates/Applications
Decline Reasons for Candidates/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.
Active Recruiting Pipeline - Stage breakdown
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.

 


Recommended

4 thoughts on “Recruitment Manager Excel Template – Most Frequently Asked Question

  1. 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?

    1. Thanks.
      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.

  2. what if I change the name of one column in recruiting stage, or maybe remove it. will this effect the template overall.

    1. 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.
      Best wishes.

Leave a Reply

Your email address will not be published. Required fields are marked *