How to Build a Recruitment Tracker in Excel for Hiring

A recruitment tracker in Excel is one table of candidates plus a small summary. Each row is a candidate for a role, with their source, current stage and key dates. Drop-down lists keep the stages consistent, DATEDIF measures days in process, and COUNTIFS turns the list into a pipeline summary by role and stage. This guide builds it step by step for recruiters, HR assistants and hiring managers who need a clear view without special software.

What a recruitment tracker needs#

Decide the columns before you type any data. Each column should answer a question someone will actually ask. A practical starting set:

The formulas below use these column letters. The exercise on this page uses a shorter four-column version.

Use one row per candidate per role. If someone applies for two roles, give them two rows with the same candidate ID.

Candidate data is personal data. Keep only the columns you need, store the file where only the right people can open it, and follow your organisation's policies on how long to keep it. Keep notes short, factual and professional.

Step 1: Set up the table#

Type your headers in row 1, add a few rows of data and turn the range into a table with Ctrl+T (Mac: ⌘T). A table grows when you add a candidate at the bottom, keeps formulas filling down and makes filtering easy.

Freeze the header row with View > Freeze Panes > Freeze Top Row so the column names stay visible as the list grows. Format all date columns the same way, such as d mmm yyyy, so dates are easy to read.

Step 2: Add drop-down lists for stage and source#

Typing stages by hand causes most tracker problems: "Interview", "interview " and "Intvw" all count as different stages. Drop-down lists fix that.

1. On a sheet called Lists, type the stages in order: Applied, Screen, Interview, Offer, Hired, Rejected, Withdrawn.
2. Select the Stage column in your tracker.
3. Go to Data > Data Validation, choose Allow: List and set the source to the stage cells, such as =Lists!$A$2:$A$8.
4. Repeat for Source (Referral, Website, Job board …) and Role.

The leave tracker guide uses the same drop-down approach for leave types, and the sales pipeline tracker uses it for deal stages.

Step 3: Calculate days in process and time to hire#

Two date measures cover most questions: how long candidates have been waiting, and how long hiring took.

Time to hire is the gap between the applied date and the hire date. With Applied in column F and Hired in column J:

=IF(J2="","",DATEDIF(F2,J2,"d"))

For a candidate who applied on 2 March 2026 and was hired on 10 April 2026, this returns 39 days. The IF leaves the cell blank until there's a hire date. Plain subtraction, =J2-F2, gives the same number. The DATEDIF guide shows the other units, such as "m" for whole months.

Working days exclude weekends. Use =NETWORKDAYS(F2,J2), which counts both the start and end dates, and add a holiday range as a third argument if you need it.

Days since the last update helps spot candidates who have been waiting a long time. With the last update date in column K:

=TODAY()-K2

Format the result as a number, not a date. Because TODAY changes every day, this column always shows the current wait.

Step 4: Summarise the pipeline with COUNTIFS#

A summary sheet answers "how many candidates are where?" without filtering. COUNTIFS counts rows that match several conditions. For Analyst candidates at the Interview stage:

=COUNTIFS(Tracker!$C$2:$C$200,"Analyst",Tracker!$E$2:$E$200,"Interview")

To build a full grid, put the roles down column A of the summary sheet and the stages across row 1, then use cell references in B2:

=COUNTIFS(Tracker!$C$2:$C$200,$A2,Tracker!$E$2:$E$200,B$1)

Fill it across and down. The mixed references ($A2 and B$1) keep each formula pointing at its own role and stage. The COUNTIFS guide explains the syntax. If you'd rather click than type, a pivot table with Role in Rows and Stage in Columns gives the same grid.

Step 5: Measure the funnel with stage dates#

A Stage column only stores where each candidate is now. Someone who was interviewed and then rejected shows as Rejected, so counting "Interview" undercounts how many people were interviewed.

That is why the table has a date column for each stage. To count everyone who reached the interview stage, count the filled dates in the Interview date column (H):

=COUNT(Tracker!$H$2:$H$200)

COUNT only counts numbers, and real dates are numbers, so blank cells are skipped. Divide one stage by the one before it for a stage-to-stage rate, such as interviews ÷ screens. Small numbers swing a lot, so show the counts next to any percentage.

Step 6: Flag candidates who need attention#

Conditional formatting can highlight rows that need a follow-up. Select the data rows, go to Home > Conditional Formatting > New Rule > Use a formula, and enter:

=AND($E2<>"Hired",$E2<>"Rejected",$E2<>"Withdrawn",TODAY()-$K2>7)

Every open candidate whose last update was more than 7 days ago gets a fill color. The $ before each column letter makes the whole row check the same columns. Change 7 to match your team's follow-up target. The appointment tracker guide uses a similar rule for upcoming dates.

Keep the tracker reliable#

A tracker is only useful if people keep it up to date. A few habits help:

• Update the stage and its date together, every time.
• Don't delete rows for rejected candidates; set the stage instead, so your counts stay complete.
• Protect formula columns with Review > Protect Sheet if several people edit the file.
• Review the summary weekly and check that totals match the number of rows.

The same counting and date skills come up across HR work; see the Excel skills for HR jobs and HR analytics in Excel for more.

Practise it#

The skills behind this tracker are the ones HR tests check most. Practise the HR roster interview drill, where you count, total and look up staff data against the clock. Then build multi-condition counts in the COUNTIFS and SUMIFS lesson and work out tenure and gaps in the days between dates lesson. Each one runs in a live spreadsheet that checks your formula as you type.