Excel guide · 4 min read
How to build a source of hire report in Excel
=COUNTIFS($B$2:$B$15,F2), where column B holds each hire's source and F2 the source name, then divide by the total for each source's share. Add spend per source for cost per hire, and 90-day retention counted only on hires who have reached day 90. Here job boards brought 6 of 14 hires (42.9%), but only 40% of the eligible ones were still there at day 90.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 | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Hire ID | Source | Start date | Employed at 90 days | Source | Spend 2026 | Hires | Share of hires | Cost per hire | 90-day retention | Flag | |
| 2 | H-101 | Referral | Jan 12, 2026 | Yes | Referral | 6000 | 3 | 0.21428571429 | 2000 | 1 | ||
| 3 | H-102 | Job board | Jan 19, 2026 | No | Job board | 9800 | 6 | 0.42857142857 | 1633.3333333 | 0.4 | ||
| 4 | H-103 | Agency | Feb 2, 2026 | Yes | Agency | 41000 | 2 | 0.14285714286 | 20500 | Too few (2) | ||
| 5 | H-104 | Careers page | Feb 16, 2026 | Yes | Careers page | 2400 | 3 | 0.21428571429 | 800 | Too few (2) | ||
| 6 | H-105 | Job board | Mar 2, 2026 | Yes | Social ads | 3500 | 0 | 0 | No hires | Too few (0) | Spend, no hires | |
| 7 | H-106 | Referral | Mar 23, 2026 | Yes | University fair | 1800 | 0 | 0 | No hires | Too few (0) | Spend, no hires | |
| 8 | H-107 | Job board | Apr 6, 2026 | No | Total | 64500 | 14 | 4607.1428571 | ||||
| 9 | H-108 | Agency | May 4, 2026 | No | ||||||||
| 10 | H-109 | Careers page | May 18, 2026 | Yes | ||||||||
| 11 | H-110 | Job board | Jun 1, 2026 | Yes | ||||||||
| 12 | H-111 | Referral | Jul 13, 2026 | Yes | ||||||||
| 13 | H-112 | Job board | Aug 3, 2026 | No | ||||||||
| 14 | H-113 | Careers page | Sep 14, 2026 | |||||||||
| 15 | H-114 | Job board | Oct 5, 2026 |
Download this sheet as an Excel practice file (.xlsx)
The two tables you need
Source of hire answers: where do our hires actually come from, what does each channel cost, and do the people it brings stay? You need two tables.
The hires log in A:D has one row per hire: an ID (keep names out of an analysis file you'll share), the source, the start date, and whether they were still employed at day 90, filled in only once day 90 has passed. The spend table in F:G lists every source you paid for this year, including the ones that produced nobody. That's the point of listing spend separately: a source with no hires never shows up in the hires log.
Agree the source names before anyone types them. "Job board", "Jobboard" and "Indeed" will be counted as three sources. A drop-down list on column B (Data > Data Validation > List) pointing at F2:F7 keeps them consistent.
Hires and share of hires by source
In H2, COUNTIFS counts the hires log rows whose source matches F2. Lock the log range with $ so it stays put as you fill down. I2 divides by the total, =H2/SUM($H$2:$H$7), formatted as a percentage.
Job boards brought 6 of the 14 hires, 42.9%. Referrals and the careers page brought 3 each and agencies 2. To report a single quarter or half year, add a date condition on the start date, for example COUNTIFS($B$2:$B$15,F2,$C$2:$C$15,">="&DATE(2026,7,1)) for hires from July onwards.
Result: 6
6 hires came from job boards.
How do I rank the sources?
In Excel 365, SORTBY lists the sources in order of hires, largest first. It updates as new hires are logged.
Result: spills 6 rows × 1 column
| Job board |
| Referral |
| Careers page |
| Agency |
| Social ads |
| University fair |
Job board, Referral, Careers page, Agency, Social ads, University fair.
Cost per hire by source
Divide each source's spend by its hires: =IF(H2=0,"No hires",G2/H2). The IF stops a #DIV/0! for sources that produced nobody. Agencies cost 20,500 per hire, job boards 1,633.33, referrals 2,000 (the referral bonuses) and the careers page 800.
This uses direct spend only, so it won't add up to your overall cost per hire, which also includes recruiter time and shared tools. For that total, see cost per hire in Excel. Direct cost by source is the figure you need when you compare channels.
Result: 20500 shows as 20,500.00 with Number format
20,500 per hire from agencies: 41,000 of fees for 2 hires.
90-day retention by source, with a minimum sample
A cheap source that brings people who leave in the first month isn't cheap. Count only hires who have reached day 90 by the report date in O1: those who started on or before O1-90. Counting newer hires would lower every source's rate, because they can't have a Yes yet.
LET names that eligible count n. If n is below the minimum sample in O2 (3 here), the formula says "Too few" with the count instead of a percentage, because 1 out of 2 is not a 50% retention rate anyone should act on. Job boards have 5 eligible hires and 2 stayed: 40%. Referrals kept all 3. Agencies and the careers page have only 2 eligible hires each, so they show Too few (2) for now. Raise the minimum when you have more data.
Result: 0.4 shows as 40% with Percent format
0.4, shown as 40%: 2 of the 5 job board hires who have reached day 90 were still employed.
What if I don't have Excel 365 for LET?
Put the eligible count in its own helper column with the COUNTIFS on the source and start date, then refer to that cell in the IF. The result is the same.
Flag sources with spend but no hires
Social ads and the university fair cost 5,300 between them this year and produced no hires. In the hires log they're invisible; in the spend table, a simple test catches them: spend above 0 and hires equal to 0.
A flag isn't a verdict. A university fair might fill next summer's graduate roles, and a new ad campaign may need time. But a line of spend with nothing against it is the first thing to ask about. To list just those sources in Excel 365, use =FILTER(F2:F7,(G2:G7>0)*(H2:H7=0)).
Result: Spend, no hires
Spend, no hires: 3,500 on social ads and no hires from them.
Common mistakes
Inconsistent source names
Use a drop-down list for the source column. Typos and brand names split one source into several.
Counting recent hires in retention
Only hires who started at least 90 days before the report date can have a 90-day outcome. Leave the rest out of the rate.
Reading percentages from tiny samples
Set a minimum number of eligible hires and show "Too few" below it.
Building the report from the hires log alone
Sources with spend and no hires never appear there. List every paid source in the spend table.
FAQ
How do I calculate source of hire in Excel?
Count hires per source with COUNTIFS on the source column of your hires log, then divide each count by the total number of hires for the share.
What is a source of hire report?
A summary of where hires came from (referrals, job boards, agencies, your careers page and so on), usually with hires, share of hires, cost per hire and retention for each source.
How do I calculate 90-day retention for new hires?
Take the hires who started at least 90 days before the report date and divide those still employed at day 90 by that number. COUNTIFS with a start date condition of "<="&(report date - 90) does both counts.
Which recruiting source is best?
It depends on the roles, the volume and what you value: speed, cost or how long people stay. The report shows the trade-offs; read it with recruiters and hiring managers rather than ranking on one column.
Functions used
COUNTIFSCounts with several conditionsSUMAdds numbersIFPicks a result from a testANDTrue if all tests are trueLETNames values inside a formulaDATEBuilds a dateSORTBYSorts by other columns, spillsFILTERKeeps matching rows, spills
Related guides
- How to calculate recruiting funnel conversion rates in Excel
- How to calculate cost per hire in Excel
- How to calculate candidate pass-through rate by interviewer in Excel
- How to calculate time to hire in Excel
- How to calculate backorder rate in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- How to find dead stock in Excel
Updated