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

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.

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

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

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

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.

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.

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.

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.

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.

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.

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

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.

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

• The following formula returns the total number of days of the 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.

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

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

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

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

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.

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.

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.

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

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

• We chose the Red color for the REJECTED status.

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

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

• After that, add a new entry. Here, we inserted an applicant named “KIM” and her other information.

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

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

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

<< Go Back to Excel HR Templates | Excel Templates