Cellwise Start free

Excel guide · 4 min read

How to build a source of hire report in Excel

Quick answerCount hires per source from your hires log with =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.

RowABCDEFGHIJKL
1Hire IDSourceStart dateEmployed at 90 daysSourceSpend 2026HiresShare of hiresCost per hire90-day retentionFlag
2H-101ReferralJan 12, 2026YesReferral600030.2142857142920001
3H-102Job boardJan 19, 2026NoJob board980060.428571428571633.33333330.4
4H-103AgencyFeb 2, 2026YesAgency4100020.1428571428620500Too few (2)
5H-104Careers pageFeb 16, 2026YesCareers page240030.21428571429800Too few (2)
6H-105Job boardMar 2, 2026YesSocial ads350000No hiresToo few (0)Spend, no hires
7H-106ReferralMar 23, 2026YesUniversity fair180000No hiresToo few (0)Spend, no hires
8H-107Job boardApr 6, 2026NoTotal64500144607.1428571
9H-108AgencyMay 4, 2026No
10H-109Careers pageMay 18, 2026Yes
11H-110Job boardJun 1, 2026Yes
12H-111ReferralJul 13, 2026Yes
13H-112Job boardAug 3, 2026No
14H-113Careers pageSep 14, 2026
15H-114Job boardOct 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.

=COUNTIFS($B$2:$B$15,F3)

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.

=SORTBY(F2:F7,H2:H7,-1)

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.

=IF(H4=0,"No hires",G4/H4)

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.

=LET(n,COUNTIFS($B$2:$B$15,F3,$C$2:$C$15,"<="&($O$1-90)),IF(n<$O$2,"Too few ("&n&")",COUNTIFS($B$2:$B$15,F3,$C$2:$C$15,"<="&($O$1-90),$D$2:$D$15,"Yes")/n))

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)).

=IF(AND(G6>0,H6=0),"Spend, no hires","")

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

Related guides

Updated