Cellwise Start free

Excel guide · 4 min read

How to calculate recruiting funnel conversion rates in Excel

Quick answerGive each stage a number (Applied 1, Screen 2, Interview 3, Offer 4, Hired 5), record each candidate's furthest stage, and count everyone who reached a stage or beyond with =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.

RowABCDEFGHIJK
1CandidateSourceFurthest stageStatusStage #StageOrderReached (closed)Target hires
2Ana P.Job boardAppliedClosed1Applied1123
3Ben O.Job boardScreenClosed2Screen29
4Chloe R.Job boardInterviewClosed3Interview36
5Dev S.Job boardAppliedClosed1Offer43
6Erin M.Job boardScreenClosed2Hired52
7Femi A.Job boardOfferClosed4
8Grace L.ReferralHiredClosed5
9Hugo T.ReferralInterviewClosed3
10Isla W.ReferralInterviewActive3
11Jon K.LinkedInScreenClosed2
12Kira N.LinkedInHiredClosed5
13Leo B.LinkedInAppliedActive1
14Maya D.LinkedInInterviewClosed3
15Nate F.Job boardAppliedClosed1

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.

=COUNTIFS(E2:E15,">="&3,D2:D15,"Closed")

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.

=I5/I4

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.

=I6/I2

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.

=COUNTIFS(E2:E15,">="&4)/COUNTIFS(E2:E15,">="&3)

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.

=COUNTIFS(B2:B15,"Referral",E2:E15,5,D2:D15,"Closed")/COUNTIFS(B2:B15,"Referral",D2:D15,"Closed")

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.

=ROUNDUP(K2/(I6/I2),0)

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

Updated