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

If you are looking for some special tricks to create a recruitment tracker in Excel, you’ve come to the right place. There is one way to create a recruitment tracker in Excel. This article will discuss every step of this method to create a recruitment tracker in Excel. Let’s follow the complete guide to learn all of this.


Download Template

Download this template exercise while you are reading this article.


Step-by-Step Procedure to Create a Recruitment Tracker in Excel

In the following section, we will use one effective and tricky method to create a recruitment tracker in Excel. This section provides extensive details on this method. You should learn and apply all of these to improve your thinking capability and Excel knowledge.


Step 1: Make Dataset for Recruitment Tracker in Excel

To create a recruitment tracker, we have to follow some specified rules. At first, we want to make a dataset for the recruitment tracker. To do this we have to follow the following rules.

  • Firstly, type your required Headline fields for your data. Click here to see a screenshot that illustrates what the fields look like.

  • Next, click on the Insert tab and then select Table.

How to Create a Recruitment Tracker in Excel

  • When the Create Table dialog box opens, select a range of the cells and check My table has headers. Next, click on OK.

  • As a consequence, you will be able to create a table.

  • Next, input the applicant’s ID one by one. Then, select the range of the cells where we want to input data and then select Date to change the cell format.

  • 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 particle in the level-2 task. In the following picture, we can see all the participants except ID 3313 pass the level-1 task. Due to this, in the level-2 column, the task date for ID 3313 is not assigned.
  • In the following picture, we can see only six 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 and who is rejected after the interview. Then, we input the reasons for rejecting the candidates in the interview in the REJECTION REASON column. Finally, we will get the following dataset for our recruitment tracker.

Make a Dataset for Recruitment Tracker in Excel


Step 2: Build Tracker List for Company

We want to make a tracker that takes off our loads to a great extent as we don’t have to do manual updates on certain calculations. In this step, we will use COUNTA and IF functions. To do this you have to follow the following steps.

  • Firstly, we have to input job details to create a recruitment tracker. We have to input the company name, job title, owner, the application started date, and hired date like the following picture.

  • Next, we want to create a drop-down arrow in the APPLICANTS ID. To do this, select Data > Data Tools > Data Validation.

  • When the Data Validation dialog box opens, select List in the Allow section and select the Applicants ID cells as a range of cells in the Source field. Click on OK.

  • As a consequence, you will get the following drop-down arrow in APPLICANTS ID.

  • To rename the dataset table we have to go to the Table Design tab and type R_trac in the Table Name box.

  • To rename the applicant id column we have to go to the Formulas tab and select Name Manager.

How to Create a Recruitment Tracker in Excel

  • When the Edit Name dialog box opens, type APP in the Name box. Click on OK.

  • To count how many applicants join the exam we have to use the following formula in cell B11.

=COUNTA(APP)

The COUNTA function will return a value by counting the cells which are specified in the above formula.

  • Press Enter.

How to Create a Recruitment Tracker in Excel

  • To calculate the Total days to hire, we have to use the following formula in cell C11.

=C6-C5

  • Press Enter.

  • To get each applicant’s performance level, we have to use the following formula in cell H16.

=IF([@[OFFER DATE]]>0,$L$11,IF([@[INTERVIEW DATE]]>0,$L$10,IF([@[LEVEL-2]]>0,$L$9,IF([@[LEVEL-1]]>0,$L$8))))

This Nested IF function returns the performance level of each applicant.

  • Press Enter.
  • As a result, you will get the LEVELS column.

Build Tracker List for Company


Step 3: Generate Dynamic Recruitment Tracker

Now, we want to create a dynamic recruitment tracker by creating an applicant pipeline and recruitment levels. In this step, we will use COUNTIFS and SUM functions. To do this we have to follow the following steps.

  • Firstly, to calculate the applicants’ pipeline (means available candidates who participate in interviews after passing the level-2 task), we have to use the following formula in cell C5 after opening a new sheet.

=COUNTIFS(R_trac[CURRENT STATUS],"",R_trac[LEVELS],B5)

This COUNTIFS function returns how many candidates stand in different levels of tasks by counting the dataset whose name is R-trac (we previously renamed the dataset as R_trac).

  • Press Enter.

  • Next, drag the Fill Handle icon.
  • As a result, we will get the applicants pipeline like the following.

  • To create a Pie chart, select a range of cells and click on the Insert tab. Next, select the 3-D Pie chart.

How to Create a Recruitment Tracker in Excel

  • Next, to modify the chart style, select Chart Design and then, select your desired Style2 option from the Chart Styles group.
  • To move the legends at the top, we have to select Add Chart Element, select legend, and then select Top.

How to Create a Recruitment Tracker in Excel

  • As a result, you will get the following 3-D pie chart.

Generate a Dynamic Recruitment tracker

  • To create a Doughnut chart, select the range of data and click on the Insert tab. Next, select the Doughnut chart.

  • As a consequence, you will get the following Doughnut chart.

Generate a Dynamic Recruitment tracker

  • Now, we want to create a recruitment levels (each candidate’s performance level) chart. To do this, we have to use the following formula in cell E5.

=COUNTIFS(R_trac[LEVELS],B5)

This COUNTIFS function returns how many candidates stand in different levels of tasks by counting the dataset whose name is R-trac (we previously renamed the dataset as R_trac).

  • Press Enter.

How to Create a Recruitment Tracker in Excel

  • Drag down the Fill Handle icon.
  • As a result, you will get how many applicants stand at different levels.

  • To calculate the totals of applicants in different levels, we use the SUM function in cell F5.

=SUM(E5:$E$8)

  • Press Enter.

  • Next, drag down the Fill Handle icon.
  • As a result, you will get the following column.

How to Create a Recruitment Tracker in Excel

  • To create a Pie chart, select a range of cells and click on the Insert tab. Next, select the 3-D Pie chart.

How to Create a Recruitment Tracker in Excel

  • As a result, you will get the following recruitment levels chart.

Generate a Dynamic Recruitment tracker


Step 4: Create Recruitment Tracker Summary

Now, we will create a recruitment tacker summary. To do this, we are going to show our charts in a new sheet as a report.

  • To create a dynamic recruitment summary, at first, you have to create a new sheet and set the name of that sheet as a Recruitment Tracker.
  • Next, you have to every chart by pressing ‘Ctrl+C’ and go to the Recruitment Tracker sheet, and press ‘Crl+V’ to paste it.
  • As a consequence, you will get the final output like the following.

Read More: How to Keep Track of Clients in Excel (Download Free Template)


Conclusion

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.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

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.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo