Excel guide · 4 min read
How to calculate recruiting funnel conversion rates in Excel
=COUNTIFS(E2:E15,">="&3,D2:D15,"Closed"). Stage-to-stage conversion is one stage's count divided by the one before; overall yield is hires divided by applicants. Leave candidates still in process out, or the rates look worse than they are.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Candidate | Source | Furthest stage | Status | Stage # | Stage | Order | Reached (closed) | Target hires | ||
| 2 | Ana P. | Job board | Applied | Closed | 1 | Applied | 1 | 12 | 3 | ||
| 3 | Ben O. | Job board | Screen | Closed | 2 | Screen | 2 | 9 | |||
| 4 | Chloe R. | Job board | Interview | Closed | 3 | Interview | 3 | 6 | |||
| 5 | Dev S. | Job board | Applied | Closed | 1 | Offer | 4 | 3 | |||
| 6 | Erin M. | Job board | Screen | Closed | 2 | Hired | 5 | 2 | |||
| 7 | Femi A. | Job board | Offer | Closed | 4 | ||||||
| 8 | Grace L. | Referral | Hired | Closed | 5 | ||||||
| 9 | Hugo T. | Referral | Interview | Closed | 3 | ||||||
| 10 | Isla W. | Referral | Interview | Active | 3 | ||||||
| 11 | Jon K. | Screen | Closed | 2 | |||||||
| 12 | Kira N. | Hired | Closed | 5 | |||||||
| 13 | Leo B. | Applied | Active | 1 | |||||||
| 14 | Maya D. | Interview | Closed | 3 | |||||||
| 15 | Nate F. | Job board | Applied | Closed | 1 |
Download this sheet as an Excel practice file (.xlsx)
One row per candidate, furthest stage only
The sheet above is a single role: 14 candidates, where they came from, the furthest stage each one reached and whether they're still in process. Don't keep a column per stage with ticks; one "furthest stage" column is faster to maintain and impossible to fill in inconsistently.
Column E turns the stage name into its position in the stage table (G2:G6) with =MATCH(C2,$G$2:$G$6,0). Numbers let you ask "reached Interview or later", which is what a funnel needs. Use Data > Data Validation > List on column C so nobody types "Phone screen" or "interviewed".
Step 1: Count who reached each stage, not who is sitting at it
Counting the stage name tells you who stopped there. Four candidates have "Interview" as their furthest stage, but seven candidates were interviewed: the ones who went on to an offer or a hire were interviewed too. A funnel counts everyone at that stage or beyond.
Result: 6
6 closed candidates reached the interview stage. Column I does this for every stage: 12 applied, 9 screened, 6 interviewed, 3 offered, 2 hired.
Step 2: Stage-to-stage conversion and overall yield
Divide each stage's count by the one above it. Format as percentages. The drop between two stages shows where the process loses people: a weak screen-to-interview rate points at the job ad or the screening criteria; a weak offer-to-hire rate points at pay, speed or the offer itself.
Here, 75% of applicants passed screening, 66.7% of those were interviewed, 50% of interviewees got an offer, and 66.7% of offers were accepted.
Result: 0.5 shows as 50% with Percent format
50%: 3 offers from 6 interviews.
Overall yield
Hires divided by applicants: what share of everyone who applied ended up hired.
Result: 0.16666666667 shows as 16.7% with Percent format
16.7%: 2 hires from 12 closed applicants.
Keep candidates still in process out of the rates
Isla is at interview stage and still active. If you count her, she looks like an interviewee who didn't get an offer, when really nobody knows yet. Including active candidates makes every late-stage rate look worse, and the effect is biggest for a role you just opened.
Without the Status condition, interview-to-offer comes out at 42.9% instead of 50%. That's why every COUNTIFS above has D2:D15,"Closed". Report active candidates separately as your pipeline: =COUNTIFS(D2:D15,"Active") says 2 people are still in play.
Result: 0.42857142857 shows as 42.9% with Percent format
42.9%, the misleading version with active candidates counted. The closed-only rate is 50%.
Conversion by source
Add the source as one more COUNTIFS condition. Volume and quality are different questions: the job board brought 7 of the 12 closed applicants, but only 28.6% of them reached interview, against 66.7% from LinkedIn and 100% from referrals.
With samples this small, treat the source split as a hint, not a conclusion. A few dozen candidates per source is the minimum before a percentage means much.
Result: 0.5 shows as 50% with Percent format
50% of closed referral candidates were hired (1 of 2). LinkedIn's yield is 33% and the job board's is 0%.
How many applicants does the next role need?
Work backwards from the target. If 16.7% of applicants end up hired, three hires need three divided by that yield. ROUNDUP, because you can't attract half a candidate and rounding down would leave you short.
Put the target in an input cell (K2) and the plan updates itself. If you use stage rates instead, divide the target by each rate in turn from the bottom of the funnel up: offers needed, then interviews, then screens, then applicants.
Result: 18
18 applicants for 3 hires, if this role's funnel holds. For time and money per hire, see the time-to-hire and cost-per-hire guides.
Common mistakes
Counting the stage name instead of "this stage or later"
Use stage numbers and ">="&n in COUNTIFS. Otherwise hired candidates don't count as interviewed.
Including active candidates
They haven't finished, so they drag every later rate down. Add a Status condition and report the pipeline separately.
Free-typed stage names
"Interview" and "Interviewed" don't match. Restrict the column with a Data Validation list from the stage table.
Reading percentages from tiny samples
One hire out of two looks like 50%. Show the counts next to the rates and wait for more data before changing sources.
FAQ
How do I calculate conversion rates for a hiring funnel?
Count candidates who reached each stage or beyond, then divide each count by the stage before it. Overall yield is hires divided by applicants.
What is applicant-to-hire ratio?
The number of applicants per hire, the inverse of yield. With 12 applicants and 2 hires it's 6 to 1. It's the number you multiply by your target hires to plan sourcing.
Should rejected and withdrawn candidates be counted?
Yes. They are closed, and their furthest stage is part of the funnel. Only candidates still in process are left out of the rates.
Can I build this with a pivot table?
A pivot table counts candidates by furthest stage, which is people who stopped there, not people who reached it. Use COUNTIFS with ">=" for the funnel, or add a running total in the pivot from the bottom stage up.
Functions used
Related guides
- How to build a source of hire report in Excel
- How to calculate candidate pass-through rate by interviewer in Excel
- How to calculate cost per hire in Excel
- How to calculate time to hire in Excel
- How to calculate offer acceptance rate in Excel
- How to analyze Likert scale data in Excel
Updated