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

User Guide: How to enter data in Recruitment Manager Template?

Posted on

In this article, we will discuss step by step how to use the Recruitment Manager Excel Template.

 

DESIGN

The template focuses on two key pieces of input information – Jobs and Applications, entered in Jobs and Application sheets respectively. There are two key output sheets. The Dashboard presents a single page summary of Recruiting metrics, with several ways to filter/drill down. The Job Report sheet allows choosing one job to see the details of that specific job.

 

OVERVIEW OF STEPS

  1. Enter info. In Settings sheet (Recruitment Stages, Application Sources and Decline Reasons)
  2. Enter information of jobs to be filled in the Jobs sheet.
  3. Enter every application you receive in the Applications sheet.
  4. As you evaluate each application and move it from one stage to next, enter the date in Applications sheet.
  5. When a final decision is made on a candidate, enter ‘HIRED’ or ‘NOT HIRED’ in the Application Status column.  Enter the Decline Reason if the candidate was not hired.
  6. When a Job order is completely filled, enter in Jobs sheet: ‘COMPLETED’ in the Status field and cost of filling the Job in Cost field.
  7. View Dashboard anytime to view the performance of recruitment process (Press Ctrl+Al+F5) to refresh calculations.
  8. View Job Report sheet for the summary of comments on Candidates for one job at a time.
  9. (Optional) Use Candidates sheet to store information about candidates.

 

DETAILED STEPS

STEP 1: Enter Settings information

Recruitment Stages: The template can accommodate up to 6 stages in recruiting process. If you have only 4 stages, just clear the last two values.

Customize Recruitment Stages
Customize Recruitment Stages

Application Sources: This will be the list of sources where you receive applications. You can customize the list as needed. Enter up to 15 sources.

Customize Application Sources
Customize Application Sources

Decline Reasons: This will be the list of reasons we provide when decide not to hire a candidate. You can customize the list as needed. Enter up to 15 reasons.

Customize Decline Reasons
Customize Decline Reasons

 

STEP 2: Enter Jobs

In the Jobs sheet, you will enter each job in a new row with the following information.

Fields available: JOB ID, JOB TITLE, JOB LOCATION, HIRING MANAGER, DEPARTMENT, JOB TYPE, JOB LEVEL, RECRUITER NAME, JOB POSTED DATE, POSITIONS, STATUS, COST

Recruitment Manager Excel Template - Jobs Table
Recruitment Manager Excel Template – Jobs Table

 

Most fields are self-explanatory. The last three need some explanation.

Positions: This template can handle multiple openings of same title. For example, if you have 3 openings for Graphic Designer, you can enter them in one row and enter 3 in Positions field/column.
Status: When you enter a new job to be filled, enter ‘OPEN’ as Status. When all the positions for that job are filled, enter ‘COMPLETED’ as Status. If the job is cancelled, enter ‘CANCELLED’.
Cost: After the job is filled, enter the total cost incurred in the recruiting for that job.

 

STEP 3: Enter Applications

Enter each application you receive as a new row in the APPLICATIONS sheet. Start entering from Row 6. Please do not modify Row 5, as it will break calculations.

• Enter Job ID for which the application was sent.
• Enter Applicant name and the source through which you received the application.

Recruitment Manager Excel Template - Applications 1
Recruitment Manager Excel Template – Applications 1

 

STEP 4: Track Progress of Applications

In the next 6 columns, enter the dates on which the applicant crossed each of the recruitment stages.

Recruitment Manager Excel Template - Applications - Stage Dates
Recruitment Manager Excel Template – Applications – Stage Dates

 

For example, in the image above, you can see the dates on which each candidate crossed the 6 stages. If a candidate does not reach a stage, please don’t enter anything in that column.

 

STEP 5: Update Application Status

Recruitment Manager Excel Template - Applications - Status, Reasons and Comments
Recruitment Manager Excel Template – Applications – Status, Reasons and Comments

When a decision is made on a candidate, update APPLICATION STATUS column. Enter ‘HIRED’ if hired or ‘NOT HIRED’ if decision was taken not to hire the person. Enter the decline reason if the candidate was not hired. For example, if the candidate reaches the interview stage but does not get the offer due to ‘Technical’ knowledge , then enter ‘Technical’ as Decline Reason.

Please enter any comments or notes about the application in the COMMENTS column.

 

Error Checks

While entering data in steps 4 and 5 , please make sure that there are no errors. The template assigns applications as ‘ERROR’ (see column Q named ERROR) if one or more of the following conditions apply.

  • Subsequent stage date is earlier. Example: Stage 2 Completion Date is before Stage 1 Completion Date
  • Application Status = ‘HIRED’ and all stages are not completed.
  • Application Status = ‘NOT HIRED’ and a stage is missing. Example: Stage 4 has Completion Date but Stage 3 does not have a Completion Date.
Error Calculation for Applications
Error Calculation for Applications

 

Those Applications with ERRORs will not be reported in Dashboard.

 

STEP 6: Update Job

When a job order is fully completed, go back to the Jobs sheet and update.

Status: When all the positions for that job are filled, enter ‘COMPLETED’ as Status. If the job is cancelled, enter ‘CANCELLED’.
Cost: After the job is filled, enter the total cost incurred in the recruiting for that job.

 

STEP 7: View Dashboard

View Dashboard anytime to view performance metrics. The dashboard is fully automated. Since it uses pivot tables for calculations, we have to refresh it whenever we added/updated input data on Jobs and Applications. Please refresh it by (DATA ribbon –> Refresh All button) or keyboard shortcut (Ctrl+Alt+F5).

Recruitment Manager Excel Template - Data Refresh
Recruitment Manager Excel Template – Data Refresh

 

Recruitment Manager Excel Template - Dashboard
Recruitment Manager Excel Template – Dashboard

 

The dashboard is very flexible as it allows you to drill down using the filters. There are 10 filters/slicers available to analyze the data in detail and make smarter business decisions.

Dashboard - Slicers (or Filters)
Dashboard – Slicers (or Filters)

If the dashboard does not reflect the data entered, please refresh the calculations first. Then, check if there are ERRORs in the Applications (see column Q).

 

STEP 8: View Job Report

In the Job Report sheet, we can choose a Job ID and view all details for that one job. We can also see the Candidates and comments for each stage. This can be a useful tool to have a meaningful discussion with the stakeholders on the applications and thus take decisions.

HR Template - Job Report
Job Report

 

STEP 9 (Optional): Enter Candidates’ data

The Candidates sheet available is optional. We can store candidates’ contact information and other notes such as Skills, Strengths and Weaknesses. We can add new columns as needed.

Recruitment Manager Excel Template V2 – Support Page

Posted on

Thanks for visiting the support page for Recruitment Manager Excel Template which can help simplify your recruitment process tracking.

The following are the support articles to help you get started with using the template. If there are any questions not addressed here, please drop a comment and I will add article/video demo about that topic.

Support Articles

  • Please see this article on 5 Important Tips before using the template
    1. Do not edit calculated cells
    2. Input Data is always visible & can be edited easily
    3. Backup by saving file regularly
    4. Do not rename fields used in Pivot Tables
    5. Refresh Calculations when Pivot Tables are used
  • This template uses Excel tables for data entry. If you are not very familiar with Excel tables, please see this article on How to enter data using Excel Tables?
    • What is a Table?
    • Why do we use Tables?
    • Components of a Table
    • 3 Basic Data Entry Operations
    • 5 Essential Tips to avoid mistakes
  • Step by Step User Guide 
    • Step by Step instructions on entering data
  • Most Frequently Asked Question (Answered)
    • Why Dashboard does not reflect the data I entered in Jobs and Applications sheets?

If you have any questions on the template, please post them in the comments section below. I will respond as soon as I can. If it will benefit others, I will also publish a blog post and video answering your question.

Enhancements in Recruitment Manager Excel Template V2

Posted on

As with other templates, we continue to improve our products based on customer feedback. I am glad to announce that the V2 of the Recruitment Manager Excel Template is available now.

Recruitment Manager Excel Template v2
Recruitment Manager Excel Template v2

 

The following are the key enhancements in this version.

  • Supports 6 recruitment stages now (compared to 5)
  • Handles multiple positions of same job title. Example: You can create a job order for 3 Software Developers and manage applications together.
  • Improved Dashboard with 10 slicers/filters and summary metrics.
  • Pipeline Efficiency Metrics: Time spent in each stage is calculated now. This will help identify bottlenecks in the process.
  • Job Report: Summarizes the details of one job in a single sheet that is printable.
  • Works in Excel for Mac (2016 and above), Excel for Windows (2010 and above)
  • Improved speed due to optimized back-end calculations

 

Thanks to everyone for the feedback. As always, existing customers will receive the new upgraded file by email for free. If you didn’t receive by mistake, please contact me and I will send it to you. Please mention the email address used in the order. Thank you.

Recruitment Manager – Excel Template – Support

Posted on

This is the support page for the V1 of Recruitment Manager Excel Template. A new and improved V2 is available now. Please visit the product page for details and the new Support Page for V2.

 

 

SIMPLIFIED DATA ENTRY

The template focuses on three key pieces of input information – Jobs, Candidates and Applications. There are three sheets (one for each) where you would enter the corresponding data.

JOBS

In the JOBS sheet, you will enter each job in a new row with the following information. (Job ID, Job Title, Job Location, Hiring Manager, Department, Job Type, Job Level, Recruiter Name, Job Posted Date)

Recruitment Manager Excel Template - Jobs sheet
Recruitment Manager Excel Template – Jobs sheet

CANDIDATES

Enter each candidate in a new row in the CANDIDATES sheet.

Recruitment Manager Excel Template - Candidates sheet
Recruitment Manager Excel Template – Candidates sheet
  • Enter an ID for each candidate
  • Enter the first name and last name.
  • Enter e-mail address and phone number of the candidate.
  • There are additional columns where you can store other information about candidates (for example, skill set, salary requirements, preferences, etc.)

APPLICATIONS

Enter each application you receive as a new row in the APPLICATIONS sheet. Please make sure that one candidate is entered only once for a job.

Recruitment Manager Excel Template - Applications sheet
Recruitment Manager Excel Template – Applications sheet
  • Enter Job ID for which the application was sent.
  • Enter Candidate name and the source through which you received the application. 
  • In the next 4 columns, enter the dates on which the applicant crossed each of the recruitment stages. For example, in the image above, you can see the dates on which each candidate crossed the 4 stages (Application, Phone Screen, Interview, Offer) before the final ‘Hired’ stage. If a candidate does not reach a stage, please don’t enter anything in that column.Recruitment Manager Excel Template - Update Application
  • When a decision is made on a candidate, enter ‘CLOSED’ in the ‘Application Status’ column. Enter the decline reason if the candidate was not hired. For example, if the candidate reaches the interview stage but does not get the offer due to Culture fit, then enter Culture Fit as Decline reason. You can enter any reason value as this is not pre-set in the template. There can be up to 10 different decline reasons.
  • Enter any comments about the application in the ‘Comments’ column.

When a position is filled, go to JOBS sheet, enter HIRED in the STATUS field, then enter HIRED DT, choose HIRED CANDIDATE name and COST of filling the position.

Recruitment Manager Excel Template - Updating Jobs
Recruitment Manager Excel Template – Updating Jobs

If the position is cancelled, update job status to CANCELLED.

Recruitment Manager Excel Template - Updating Job status
Recruitment Manager Excel Template – Updating Job status

DASHBOARD

The DASHBOARD sheet presents a single page summary of open jobs and hired jobs, along with key performance metrics.

Recruitment Manager Excel Template - Dashboard Open Jobs
Recruitment Manager Excel Template – Dashboard Open Jobs

Recruitment Manager Excel Template - Dashboard Hired Jobs
Recruitment Manager Excel Template – Dashboard Hired Jobs

It is fully automated. Please refresh data (DATA ribbon –> Refresh All button) to update the dashboard calculations.

Recruitment Manager Excel Template - Data Refresh
Recruitment Manager Excel Template – Data Refresh

OPEN JOBS – KEY METRICS

Recruitment Manager - Excel Template - Open Jobs - Key metrics
Recruitment Manager – Excel Template – Open Jobs – Key metrics

Number of Open jobs (that are not hired or cancelled) and Average number of days that these open jobs have been in market are displayed. Also, number of applications pending (without any status) and number of applications closed (status = Closed) for these open jobs are also calculated.

OPEN JOBS – RECRUITMENT FUNNEL

Recruitment Manager Excel Template - Open Jobs - Recruitment Funnel
Recruitment Manager Excel Template – Open Jobs – Recruitment Funnel

The recruitment funnel indicates how the process has been performing so far for these open jobs. This helps understand where there are issues in hiring for these jobs. Where in the process do we see candidates dropping off?

OPEN JOBS – DECLINE REASONS

Recruitment Manager Excel Template - Open Jobs - Decline Reasons
Recruitment Manager Excel Template – Open Jobs – Decline Reasons

It is always important to understand why candidates did not move forward in the recruitment funnel. This helps the recruiters find better candidates in the future.

OPEN JOBS – ACTIVE PIPELINE

Recruitment Manager Excel Template - Open Jobs - Active Pipeline
Recruitment Manager Excel Template – Open Jobs – Active Pipeline

This provides an overview of the active pipeline – candidates who are still going through the process for open jobs. For example, in the image above, out of the 14 applications, 6 are in the Application stage, 4 in the Phone Screen, 3 in Interview and 1 in Offer stages.

HIRED JOBS – KEY METRICS

Recruitment Manager - Excel Template - Hired Jobs - Key Metrics
Recruitment Manager – Excel Template – Hired Jobs – Key Metrics

Number of Applications receiver per hired position, Average number of days it took to hire and Cost per hire are calculated.

HIRED JOBS – RECRUITMENT FUNNEL

Recruitment Manager - Excel Template - Hired Jobs - Recruitment Funnel
Recruitment Manager – Excel Template – Hired Jobs – Recruitment Funnel

HIRED JOBS – TOP DECLINE REASONS

Recruitment Manager - Excel Template - Hired Jobs - Decline Reasons
Recruitment Manager – Excel Template – Hired Jobs – Decline Reasons

HIRED JOBS – TOP SOURCES

Recruitment Manager Excel Template - Top Sources
Recruitment Manager Excel Template – Top Sources

Understanding which sources are contributing to hiring and which ones are the more efficient ones (based on apps per hire) would help direct investment to the correct sources.

HIRED JOBS – TRENDS

Recruitment Manager Excel Template - Metric Trends
Recruitment Manager Excel Template – Metric Trends

You can view the last 12 months of trends from the chart where you can pick a metric out of the 4 in the drop down menu.

FILTERS

Recruitment Manager Excel Template - Dashboard Filters
Recruitment Manager Excel Template – Dashboard Filters

Recruitment Manager Excel Template - Dashboard Filters
Recruitment Manager Excel Template – Dashboard Filters

The dashboard is very flexible as it allows you to drill down using the filters. You can also choose to view one or multiple months at a time.

EASY TO RETRIEVE INFORMATION

In addition to the DASHBOARD, it is easy to retrieve information from this template.

  • You can easily identify the jobs that are currently open by filtering on status in the Jobs table.
Recruitment Manager Excel Template - Finding Open jobs
Recruitment Manager Excel Template – Finding Open jobs
  • To find the list of applications for open jobs, you can use filters similarly on the Applications table (Job Status is blank and Application status is blank).

HELPFUL ALERTS

The template provides alerts on missing data.

Recruitment Manager Excel Template - Applications Missing data
Recruitment Manager Excel Template – Applications Missing data

If the application status is CLOSED but a decline reason is not provided, there will be a red border around the decline reason cell indicating that it is required. If the job is hired, but the application status is blank, a red border will be found on the Application status cell, hinting that that needs to be CLOSED.

Recruitment Manager Excel Template - Jobs Missing data
Recruitment Manager Excel Template – Jobs Missing data

Similarly in the Jobs sheet, if you have changed a job’s status to HIRED, but have not entered the HIRED DT, HIRED CANDIDATE and COST information, there will be red borders.

 

VIDEO DEMO