I use a recruiting metrics spreadsheet when the team needs a transparent operating view and the applicant system does not make every question easy to answer. The spreadsheet is not the system of record for candidate notes. It is a controlled reporting layer with clear definitions, limited editing, and a regular review habit.
Build the workbook around separate tabs
I keep the structure simple:
- Candidate funnel: one row per candidate and requisition relationship
- Requisition summary: one row per open or recently closed role
- Source performance: applications, screens, interviews, offers, and hires by source
- Activity log: dated recruiter actions and hiring manager actions
- Definitions: metric names, field rules, owners, and update cadence
- Dashboard: a small set of current views for weekly discussion
I use a stable requisition ID and candidate ID instead of matching on names. If the workbook contains sensitive data, I restrict access and keep interview feedback in the approved recruiting system.
Candidate funnel fields
The candidate funnel tab needs fields that support both status reporting and time calculations:
| Field | Purpose |
|---|---|
| Requisition ID | Connects the candidate to the opening |
| Candidate ID | Prevents duplicate records when names change or repeat |
| Source | Records the first agreed source attribution |
| Current stage | Shows where the candidate is now |
| Stage entry dates | Supports time and conversion calculations |
| Disposition reason | Explains why the candidate left the process |
| Recruiter owner | Makes follow-up ownership visible |
| Next action and due date | Shows the immediate commitment |
| Offer and hire dates | Supports offer and outcome reporting |
I use a controlled list for stages and disposition reasons. Free-text statuses make formulas unreliable and create arguments about whether two labels mean the same thing.
Useful formulas
The exact cell references depend on the workbook, but I document formulas in the definitions tab. Examples include:
- Stage conversion rate:
completed next stage / candidates entering current stage - Offer acceptance rate:
accepted offers / offers made - Time to fill:
hire date - requisition open date - Time in stage:
next stage entry date - current stage entry date - Cost per hire:
recruiting spend / hires - Recruiter workload:
active requisitions + active candidates, shown with the chosen date filter - Aging count:
COUNTIFS(next action date, "<"&TODAY(), current stage, "<>Closed")
I label the denominator for every rate. A conversion rate based on candidates entering a stage is different from one based on candidates sourced during the same period. Both can be valid, but they answer different questions.
Define metrics before reporting them
I write down the definition, source fields, owner, and update frequency for every KPI. For example, time to fill can run from approved opening to accepted offer, hire date, or requisition close. I choose one definition for the dashboard and show the dates behind it.
I also decide how to handle:
- Candidates who apply to more than one role
- Reopened requisitions
- Internal moves and employee referrals
- Paused roles
- Agency submissions and duplicate submissions
- Withdrawn candidates and declined offers
- Missing or corrected dates
The definition tab prevents a new formula from silently changing the meaning of a metric.
Keep data clean
Each week, I check for duplicate candidate IDs, blank requisition IDs, impossible date sequences, closed candidates with open next actions, and rows with no owner. I use validation lists for stages, sources, departments, and disposition reasons. I protect formula columns and record the date of the last refresh.
I avoid copying interview opinions into a reporting workbook. A dashboard may show a disposition category, but detailed evaluation belongs in the candidate record with the right permissions.
Use a weekly review cadence
My weekly review has four parts:
- Funnel: Where are candidates moving, waiting, or dropping out?
- Sources: Which sources are producing relevant progression, not just volume?
- Speed and workload: Which next actions or feedback requests are overdue?
- Quality and experience: What disposition patterns, offer declines, or candidate complaints need attention?
I assign an owner and due date to every action. I do not use the spreadsheet to rank recruiters by raw activity without context. A difficult search, a large panel load, or a changed requirement can alter the numbers.
The workbook is doing its job when the team can trace a number back to a defined field and decide what to do next. If a metric cannot support a decision, I remove it or move it to an operational report instead of adding another chart.