Step By Step User Guide - Recruitment Dashboard Excel Template

STEP 1: Enter Settings information

Recruitment Stages

Let's begin by defining our recruitment process steps or stages.

In the Settings Sheet, we can enter the different stages.

The template can accommodate up to 10 stages in recruiting process. If you have fewer stages, you can enter only those stages and clear the other stages.

For example, in the above screenshot, we have entered 7 stages.

A few things to note:

First stage is always considered as the Application stage - the stage of receiving an application from the candidate. You can name this how you need. But for calculation purposes, this stage will be treated as the stage when the application was received from the candidate.
Last Stage (if we have 7 stages, 7th is the last stage) should be the Hired Stage. You can name this differently as you need.
The stage before the last is the Offer stage.

The above setup enables us to later easily calculate our Recruitment KPIs such as Offer rates and Acceptance rates.

Application Sources

Next, enter the the list of sources where you receive applications from. You can customize the list as needed. Enter up to 15 sources.

Decline Reasons

Documenting the reasons why a candidate was declined the offer and why candidates decline offers from the company are extremely critical for improving the process.

Enter the list of such reasons that are relevant for your company. You can customize the list as needed. Enter up to 15 reasons.

Enter currency

Enter currency symbol to display the cost information in the currency of your choice.

STEP 2: Enter Jobs or Vacancies

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

Click on image to view in full size

Click on image to view in full size

JOB ID is the unique identification of a job opening.

You can enter job information such as Job Title, Location, Hiring Manager, Department, Job Type, Job level, and Recruiter Name.

Job Posted Date is a required field. This is the date when the job is pos

Positions: This template can handle multiple openings of the same title. For example, if you have 3 openings for Graphic Designer, you can enter them in one row and enter 3 in the 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 canceled, enter ‘CANCELLED’.

STEP 3: Enter Applications or Candidates

We move to the Applications sheet.

If you indicated in Settings that there are less than 10 stages of recruitment in your company, then you will have columns that are not needed in the Applications sheet. Let's hide them first.

To make it easier for you, they are already labeled as 'Hide column'.

Select those columns, Right-click and choose Hide.

Enter each application you receive as a new row in the APPLICATIONS sheet. Start entering from Row 6.

Enter the Job ID for which the application was sent.
Enter the Candidate's name and the source through which you received the application.
Enter Application date (first stage of the recruitment process)

STEP 4: Track Progress of Applications

As you review the applications and the candidates go through each stage, just enter the dates when each candidate reaches each stage.

If a candidate does not reach a stage, please don’t enter anything in that column.

In the above screenshot, Todd Moore has reached the Tech Interview 1 stage, Thomas Meyer reached Tech Interview 2 stage, while Jimmy Lawrence has not gone past the Application stage yet.

Enter Comments or notes for each candidate in the Comments column provided.

STEP 5: 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.

This can be a useful tool to have a meaningful discussion with the recruiting team, interview panel, and the hiring manager on the applications for that job.

The Job report can list up to 40 candidates for a job.

You can also filter to candidates in a specific stage (like only those in the Hiring Mgr Interview stage).

STEP 6: Record Application Decision

When a decision is made on a candidate, update the APPLICATION STATUS column.

Enter ‘HIRED’ if hired or ‘NOT HIRED’ if the decision was taken not to hire the person.

If the status is blank, it means that the decision has not been taken yet and it is a pending application.

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.

Enter the Cost of hiring if you hire a candidate. Though the cost is incurred for the overall job and all candidates, we can keep it simple by entering the total cost in the Hired candidate's row.

Enter Comments or notes for each candidate. It's a good practice to enter notes which could be reviewed.

STEP 7: Update Job

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

Status: When all the positions for that job are filled, enter ‘COMPLETED’ as Status.

If the job is canceled, enter ‘CANCELLED’.

STEP 8: View Dashboard to gain insights

View Dashboard anytime to view performance metrics.

The dashboard is fully automated. There are 4 pages.

The first page focuses on the efficiency of the Hiring Process.

Click on the image to view it in full size.

The KPIs displayed are

Hired: Number of Hired candidates during the period chosen.
Avg. Days to Fill: Average number of days from Job Posted Date to Joining Date for all hired candidates during the period chosen.
Avg. Days to Hire: Average number of days from Application Date to Hire Date for all hired candidates during the period chosen.
Total Cost: Sum of cost entered for all hired candidates during the period chosen.
Cost per Hire: Total cost divided by the number of hired candidates during the period chosen.

We can also see the KPIs trended by month so that we can understand if we are improving on these KPIs monthly.

Days taken for each stage.

Of the average number of days taken for filling a position, we can see which stages take more time.

Sources' contribution

We can see which application source brings in most of the hired candidates. This is purely based on number of hires - quantity not quality (which we will cover later).

The second page of the Dashboard presents KPIs related to measuring the quality of applications.

The KPIs displayed are

Applications Received: Number of applications received during the period chosen.
Offer %: Number of candidates offered a position/ Number of candidates applied during the period
Acceptance %: Number of candidates accepting a position/ Number of candidates offered a position during the period
Hired %: Number of candidates hired / Number of candidates applied during the period

We have the monthly trends of these KPIs as well.

Recruitment Funnel

Analysis of Application Sources

Decline Reasons Analysis

The third page of the Dashboard focuses on Active pipeline - Open Jobs being hired, and Pending applications to make decisions on.

The KPIs displayed on this page are

Open Positions: Number of Positions that are still not filled as of now
Avg. Days in Market: Average number of days from Job Posted date to Today for open positions
Pending Applications: Number of Applications where the status is blank
Applications per position: Pending Applications divided by Number of Open positions

Active Pipeline of Pending Applications