How to Create a Recruitment Tracker in Excel (Download Free Template)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we are going to show you the step by step procedures of how to create a recruitment tracker in Excel. Recruitment tracker is important for an HR in an organization to keep the history of new employees. It is also helpful to identify the applicants who are suitable for the job, because it’s not possible to memorize the data of all the applicants.

Watch Video – Create a Recruitment Tracker in Excel

You can store the recruitment data in an Excel sheet. However, there’s a lot of applicants for a post and finding out the information of a specific applicant from the dataset is tiresome. So, you need a dashboard in your recruitment tracker. We are going to build a sample recruitment tracker template where you can search for an specific applicant and get the necessary information about him/her.

Please go through the whole article, it will be useful for you to create a recruitment tracker in Excel on your own. Here is an overview of how our tracker dashboard looks like.

How to Create a Recruitment Tracker in Excel

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

This section provides extensive details on the procedures for creating the recruitment tracker. We will be using formulas, charts, tables, drop down etc. to make the tracker dashboard easy and efficient to use. 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). We are going to show necessary calculations in these sheets in the following sections.

Step 1: Making Dataset for Recruitment Tracker in Excel

First of all, let’s begin with the sample dataset that we are going to 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 participate in these tasks to pass the requirement process barrier and to get the dream job of the company.
  • Candidates who pass the level-1 task will only be allowed to participate in the level-2 task, and so on. In the following picture, we can see all the participants except JACK (ID 3310) could not pass the level-1 task. That’s why, in the level-2 column or any other column, the task date for him is not assigned.
  • Furthermore, we can see 7 candidates pass the level-2 task, and their interview date is assigned in the INTERVIEW DATE column. Then, finally, one candidate is selected for the job and his job offer date is assigned in the OFFER DATE column.
  • Next, in the CURRENT STATUS column, we mention who is selected or rejected or if someone is considered to be hired in the future after the interview. Also, the reasons for rejection of the rejected candidates in the interview are included in the REJECTION REASON column.
  • Moreover, we 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. It means, whenever you add a new entry in a row of your data range, you don’t need to apply formula or cell formatting manually. The table will automatically calculate the value using the given formula.

  • To convert the data range to a table, select the data range and then press Ctrl + T first.
  • After that, a dialog box will pop up showing the cell range for the table.
  • Make sure you select ‘My table has headers‘ and click OK.

Creating Table from Data Range

  • After that, we will get the table view of the dataset. Here, we don’t need the Filter buttons in the header of the table. It also a bit annoying to see. So we remove them by selecting any cell on the table and then choosing the Table Design tab >> Table Styles Options >> 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. Use unique fill color for the header so that your table looks attractive.

  • Finally, we gave a suitable name for the table. This name can be used to use the table anywhere in the workbook.
  • You can give 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

Now, we are going to use a formula to determine the levels passed by the corresponding applicants. 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. Let’s observe the 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

Now, we want to create a dynamic recruitment tracker by creating an applicant pipeline and recruitment levels.

We want to insert some charts in the dashboard. These charts will be helpful to visualize the overall summary of the recruitment procedure.

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. The formula is made of the SUM function. We stored these 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 with them.

  • First, we will create a Doughnut Chart using the data of the Applicant Count column.
  • So, select the cell range C5:C8 and choose the Insert tab.
  • After that, select Doughnut Chart from the Insert Doughnut or Pie Chart group.

Creating Doughnut Chart

We need to format the Doughnut Chart to make it more user-friendly and suitable for the dashboard. Removing Fill Color from the chart background will make it transparent, and it won’t block any data from viewing in the dashboard.

  • In the chart, the Axis Labels do not seem to be meaningful. So we want to change them. We will use the B5:B8 range to label the axis.
  • For this purpose, right-click on the current Axis Labels and select ‘Select Data’ from the Context Menu.
  • Next, the Select Data Source dialog box will appear. Click on the Edit button under the Horizontal (Category) Axis Labels.
  • After that, select the data range B5:B8 to insert the data of the Stages column as labels and click OK. Finally, you will see them in the Axis Labels.

If you don’t want to follow these descriptions, just follow the video below.

You can also change the fill color of a specific portion of your chart. Say, I 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 the chart to make it bigger, and it will be easy for you to select any portion of the chart.
  • Then, right-click on it >> select Fill >> choose a color of your preference.

This operation 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.

Here is a video to show how you can change the fill color, which we described above.

After creating and formatting the Doughnut Chart, we will create a Funnel Chart which will show the number of total applicants in a specific level.

Creating Funnel Chart

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

Changing Vertical Axis Labels

Thus, the Funnel Chart has meaningful axis labels now.

Read More: How to Track Comp Time in Excel

Step 4: Setting Up Recruitment Tracker Dashboard

We will be creating the dashboard region in this section. We will apply a background color for the region. Follow the video below, we applied the Format Painter to fill the background color.

  • After that, 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

Now, we will apply a Data Validation list for the Applicants IDs. This will help us to search for a specific applicant and extract the necessary information from the Applicants_Info table.

  • To apply the Data Validation list, first select a cell to store the list and then go to Data >> Data Validation.
  • In the Data Validation dialog box, select List under the Allow drop down.

Initiating Data Validation Drop Down for Applicants ID

  • After that, select the data range containing Applicants IDs (A3:A12 in the Applicants Info sheet) for the Source.
  • Next, click OK.

Data Validation List Created

  • Thus, we create the Data Validation
  • Next, we will set a formula which will return a name corresponding to the ID selected from the Data Validation It will use 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

Here is another VLOOKUP formula that returns the status of the candidates for corresponding IDs.


Formula to Determine the Status of a Candidate

  • The next formula is a conditional formula. It uses the IFS function to return values based on some conditions. It basically generates a row header based on the cell value of cell 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”.

  • The following is the final formula for the Recruitment Tracker dashboard we are creating. Although it looks a bit complex, it actually 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”.

Next, we will add two rectangular shapes to store the charts so that they look attractive.

  • For this reason, select Insert >> Shapes and choose the rectangular shape.

Adding Rectangular Shape

Hold the mouse button and drag it to draw the rectangular shape. Give it a proper size. If you want to create another shape of the same size, just hold the Ctrl button and drag the created shape somewhere, and you will find a copy of it.

  • Now, we will apply a suitable fill color and make the shapes background. For this purpose, select both of the shapes and right-click on any of them.
  • Choose a fill color that fills the background of the shape.
  • Select Send to Back >> Send to Back to make the shape as a background of the charts.

Applying Fill Color to Shape and Making it Background

  • After that, copy the charts from the Recruitment Levels sheet, paste them, and place them on the rectangular shapes.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

In this section, we will show the use of the recruitment tracker dashboard. Please follow the video below.

In the video, we selected the IDs from the drop-down list, and the corresponding names, statuses, and rejection reasons/offer dates are appearing automatically.

Step 6: Applying Conditional Formatting Feature to Dashboard

However, we can make the results more appealing to the users. We can use conditional formatting for the STATUS of the candidates. Those who are selected, rejected, or have a chance of being hired for the recruitment process, will be highlighted differently.

  • So, first of all, select the cell containing the data of the applicants’ status and choose Conditional Formatting >> Highlight Cell Rules >> Equal To…

Applying Conditional Formatting for Specification

  • A dialog box named Equal To will pop up. Type REJECTED in the Format cells that are EQUAL TO section and choose Custom Format from the drop-down shown in the image.

Choosing Custom Color for Rejected Status

  • We chose the Red color for the REJECTED status.

Selecting Red Color from Fill Tab

  • Similarly, select fill colors for the SELECTED and STILL HIRING We chose green and yellow colors for the SELECTED and STILL HIRING statuses respectively.

Now, you will see the different background colors for different statuses. You can identify the difference easily.

Moreover, as we have built this recruitment tracker, there’s one more thing we can do to make this dashboard easier to use.

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 will use a named range for the Applicants IDs.

  • To add a name range to the IDs, just select the cell range (A3:A12) and type a name in the Name Box. We gave the name of this range Applicants_ID.

Creating Named Range for Applicants IDs

  • Next, replace the source of the Data Validation list with this named range.

Inserting Named Range to the Data Validation List

  • After that, add a new entry. Here, we inserted an applicant named “KIM” and her 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

Thus, you can create a recruitment tracker in Excel using different features.

Read More: How to Design Employee Details Form in Excel

Download Free Template


That’s the end of today’s session. I strongly believe that from now you may be able to create a recruitment tracker in Excel. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website Exceldemy for various Excel-related problems and solutions. Keep learning new methods and keep growing!

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

  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