How to Create a Recruitment Tracker in Excel: Step-by-Step Procedures


We are going to build a sample recruitment tracker template where you can search for a specific applicant and get the necessary information about them. Here is an overview of what our tracker dashboard looks like. The workbook contains three different sheets: Recruitment Tracker (Dashboard), Applicants Info (Information about the applicants are stored in a table of this sheet) and Recruitment Levels (Data to create charts are stored).

How to Create a Recruitment Tracker in Excel

Step 1 – Making the Dataset for Recruitment Tracker in Excel

Here’s a sample dataset we will use for the recruitment tracker.

Sample Dataset Containing Applicants Information

  • Here, the recruitment process contains several levels of tasks such as Level-1, Level-2, and interview tasks. Each candidate needs to progress through them in order.
  • Empty fields indicate that an applicant was rejected in the previous stage of the process.
  • The interview date is assigned in the INTERVIEW DATE column. The OFFER DATE column contains dates for candidates who passed the process.
  • The CURRENT STATUS column mentions whether a candidate is selected or rejected or if someone is considered to be hired in the future after the interview.
  • The reasons for rejection of the rejected candidates in the interview are included in the REJECTION REASON column.
  • We also made a helper column named LEVELS which will be helpful to develop formulas in the later sections.

Now, we will convert this data range to an Excel table because Excel table is dynamic to use. Whenever you add a new row to your data range, the table will automatically calculate apply the formulas and formatting.

  • Select the data range and press Ctrl + T.
  • A dialog box will pop up showing the cell range for the table.
  • Select ‘My table has headers‘ and click OK.

Creating Table from Data Range

  • Select any cell on the table and then choose the Table Design tab.
  • Go to Table Styles Options and uncheck the Filter Button.

Formatting the Table

You can see that the Filter buttons are now gone. You can also choose your own table design from the Table Styles group.

  • Give a suitable name for the table. This name can be used to use the table anywhere in the workbook. You can input the name for the table in the Properties group of the Table Design tab.

Formatted View of the Table

Step 2 – Generating Formula to Setup Necessary Parameters

If someone passes level-2 and proceeds to the interview, the formula will return “INTERVIEW“. If someone passes the level-2 but doesn’t succeed the interview, the result will be “LEVEL-2“. This employs that the candidate passed the level-1 tasks.

Use this formula:

=IFS([@[OFFER DATE]]>0,$A$18,[@[INTERVIEW DATE]]>0,$A$17,[@[LEVEL-2]]>0,$A$16,[@[LEVEL-1]]>0,$A$15)

Formula to Determine Levels Passed by Candidates

Step 3 – Creating and Formatting Dynamic Charts for Recruitment Tracker Dashboard

Let’s create a dynamic recruitment tracker by creating an applicant pipeline.

Use the formula below with the COUNTIF function to find out the number of applicants who failed in a particular stage. We stored the counts in the Applicant Count column.


Formula to Count Applicants in Different Levels

And the following formula sums up the total number of candidates who attended a particular stage. We stored this data in the Recruitment Levels column.


Counting Candidates Passing Corresponding Levels

To visualize the data of Applicant Count and Recruitment Levels columns, we will create a Doughnut Chart and a Funnel Chart respectively:

  • Select the cell range C5:C8 and choose the Insert tab.
  • Go to Doughnut Chart from the Insert Doughnut or Pie Chart group.

Creating Doughnut Chart

  • Remove Fill Color from the chart background.
  • Right-click on the current Axis Labels and select ‘Select Data’ from the Context Menu. The Select Data Source dialog box will appear.
  • Click on the Edit button under the Horizontal (Category) Axis Labels.
  • Select the data range B5:B8 to insert the data of the Stages column as labels and click OK. You will see them in the Axis Labels.

You can also change the fill color of a specific portion of your chart. Suppose we want to change the fill color of the blue portion of the chart. To do that:

  • Select the portion first. It may take some time if your chart is small, so drag out the chart to make it bigger.
  • Right-click on it, select Fill, and choose a color.

This will change the fill color of the specific part of the doughnut chart. Moreover, you can change or modify the appearance of the chart using the options from the Chart Design tab. There are various options for formatting charts.

Now we need to create a Funnel Chart which will show the number of total applicants in a specific level.

Go to Insert, click on the Chart option, and select Funnel.

Creating Funnel Chart

Change the Axis Labels following the procedure described for the Doughnut Chart.

Changing Vertical Axis Labels

The Funnel Chart has meaningful axis labels now.

Read More: How to Track Comp Time in Excel

Step 4 – Setting Up Recruitment Tracker Dashboard

  • Pick some cells to store the necessary data you want to display in the dashboard. We typed necessary headings here and used different fill color for the Applicants Data.

Rough Outline of Dashboard

  • To count the total number of applicants, use the formula below:

The formula uses the COUNTA function to count how many applicants attended for the job. The advantage of using the COUNTA function is that it doesn’t count blank cells.

Formula to Count Number of Total Applicants

  • The following formula returns the total number of days of the hiring process:

Formula to Calculate the Duration of Hiring Process

Let’s apply a Data Validation list for the Applicants IDs:

  • Select a cell to store the list, go to Data, and then to Data Validation.
  • In the Data Validation dialog box, select List under the Allow drop-down.

Initiating Data Validation Drop Down for Applicants ID

  • Select the data range containing Applicants IDs (A3:A12 in the Applicants Info sheet) for the Source.
  • Click on OK.

Data Validation List Created

  • Paste the following formula in F4 to return a name corresponding to the selected ID. It uses the VLOOKUP function to search for the ID selected in the F3 cell in the Applicants_Info table and return the name from its NAME column. The IFERROR function is used to ignore the error which can occur from the absence of data in cell F3.

Formula to Return the Name of Applicants

  • In F5, put the following formula to retrieve the status of the candidate.

Formula to Determine the Status of a Candidate

  • Copy the following formula into E6 to generate row header based on the cell value in F5.

Formula to Automate the Header of Additional Information

If the STATUS is SELECTED, the formula returns “OFFER DATE”. If it is “STILL HIRING”, then the formula returns blank. The formula also returns “REJECTION REASON” if the value of cell F5 is neither “SELECTED” nor “STILL HIRING”.

  • Put the following formula into F6, which returns REJECTION REASON or OFFER DATE for the corresponding applicant based on the value of cell E6.
=IFERROR(IF(F5="STILL HIRING","",IF(E6=Applicants_Info[[#Headers],[OFFER DATE]],XLOOKUP(F3,Applicants_Info[APPLICANTS ID],Applicants_Info[OFFER DATE]),XLOOKUP(F3,Applicants_Info[APPLICANTS ID],Applicants_Info[REJECTION REASON]))),"")

Formula to Show Rejection Reason or Offer Date

The formula uses the XLOOKUP function to return the REJECTION REASON or OFFER DATE from the Applicants_Info table. It will return blank if the cell value of F5 is “STILL HIRING”.

Let’s format the table a bit:

  • Go to Insert and select Shapes.
  • Choose the rectangular shape.

Adding Rectangular Shape

  • Hold the mouse button and drag it to draw the rectangles in the table.
  • Hold the Ctrl button and drag the created shape around to copy it.
  • Select both of the shapes and right-click on any of them.
  • Go to “Fill” and choose a fill color.
  • Select Send to Back and Send to Back again to make the shape into a background of the chart.

Applying Fill Color to Shape and Making it Background

  • Copy the charts from the Recruitment Levels sheet, paste them, and place them on the rectangles.Copying Charts and Resizing Them in Rectangular Shapes

Now, the charts have a better view to visualize for the users.

Step 5 – Using the Dashboard

  • Click on the Applicants ID drop-down (cell F3).
  • Select an ID from the drop-down list.
  • The corresponding names, statuses, and rejection reasons/offer dates will appear automatically.

Step 6 – Applying Conditional Formatting Feature to Dashboard

  • Select the cell containing the data of the applicants’ status and choose Conditional Formatting.
  • Go to Highlight Cell Rules.
  • Select Equal To… A dialog box named Equal To will pop up.

Applying Conditional Formatting for Specification

  • Type REJECTED in the Format cells that are EQUAL TO section and choose Custom Format from the drop-down on the right.

Choosing Custom Color for Rejected Status

  • We chose the Red color for the REJECTED status.

Selecting Red Color from Fill Tab

  • Repeat the process for SELECTED and STILL HIRING. We chose green and yellow colors for the SELECTED and STILL HIRING statuses, respectively.

You will now see different background colors based on applicant status.

Step 7 – Adding Named Range to Make Recruitment Tracker Template Dynamic

Say you insert a new candidate information in the Applicants_Info table. If you search for the new entry in the drop-down list of IDs, you won’t find it. That’s why we need a named range for the Applicants IDs:

  • Select the cell range (A3:A12) and type a name in the Name Box. We used Applicants_ID.

Creating Named Range for Applicants IDs

  • Replace the source of the Data Validation list with this named range.

Inserting Named Range to the Data Validation List

  • Add a new entry. Here, we inserted an applicant named “KIM” and their other information.

Inserting New Data Entry

You will find the drop-down list updated with the new ID, and selecting this will retrieve corresponding information about her.

Data Validation List and Charts Updated

Read More: How to Design Employee Details Form in Excel

Download Free Template

<< Go Back to Excel HR Templates | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

  1. This is a great post! I have been looking for a way to track my recruitment progress and this template is perfect!

Leave a reply

Advanced Excel Exercises with Solutions PDF