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.

• 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.

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.

• 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.

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

• Press Enter.

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

• Press Enter.

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

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

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

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.

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.

• 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.

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

• 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.

• 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.

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.

• 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.

• Press Enter.

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

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

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

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

• How to Track Attendance in Excel (with Detailed Steps)
• How to Keep Track of Invoices and Payments in Excel (3 Ideal Examples)
• Create Fully Functional To Do List in Excel (4 Handy Methods)
• Keep Track of Inventory in Excel (2 Easy Methods)
• How to Keep Track of Customer Payments in Excel (With Easy Steps)