Cellwise Start free

Excel guide · 5 min read

How to calculate employee turnover rate in Excel

Quick answerTurnover rate = people who left in the period / average headcount. Count the leavers with =COUNTIFS(D2:D15,">="&H2,D2:D15,"<="&H3) (leave dates between the period start and end), average the headcount at the start and the end, and divide: =H6/AVERAGE(H4,H5). Format it as a percentage.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFGH
1NameDepartmentHire dateLeave dateLeave typeSettingValue
2Ana RuizSalesMar 15, 2021Period startOct 1, 2026
3Ben OkaforSalesJun 3, 2024Oct 9, 2026VoluntaryPeriod endOct 31, 2026
4Chloe ParkSupportJan 10, 2022Headcount at start9
5Dev PatelSupportAug 17, 2026Sep 25, 2026VoluntaryHeadcount at end9
6Emma StoneOpsNov 4, 2019Leavers in period2
7Farid HaddadOpsSep 25, 2023Sep 18, 2026Involuntary
8Grace LeeSalesJul 20, 2026
9Hugo SilvaSupportOct 12, 2026
10Ivy ChenOpsMay 18, 2020Oct 30, 2026Involuntary
11Jon WeberSalesJul 6, 2026Sep 4, 2026Voluntary
12Kara NovakSupportAug 3, 2026
13Liam BrooksOpsOct 19, 2026
14Maya RossSalesAug 13, 2018
15Noah KimSupportMay 12, 2025

Download this sheet as an Excel practice file (.xlsx)

Step 1: Start from a roster with hire and leave dates

Your manager asks for October's turnover by the end of the day. The easiest source is the roster you already keep: one row per person with a hire date, and a leave date that stays blank while they're still employed. Add a Leave type column (Voluntary or Involuntary) if you want the split later.

Keep everyone on the list, including people who left months ago. Deleting leavers is the most common reason a turnover sheet stops working: the formulas below need their dates to know who was on the books on any given day.

Put the period start and end in their own cells (H2 and H3 here). Every formula points at those two cells, so next month you change two dates and the whole sheet updates.

Step 2: Headcount on any date

Someone counts as employed on a date if they were hired on or before it and either have no leave date or left after it. COUNTIFS can't say "blank OR after", so add two counts: hired by the date with a blank leave date, plus hired by the date with a leave date after it.

On Oct 1 that gives 9. Hugo and Liam aren't counted yet (they start later in October), and Dev, Farid and Jon had already left in September. On Oct 31 it's 9 again: Hugo and Liam joined, Ben and Ivy left.

The "" criterion in COUNTIFS matches empty cells. A leave date typed as a space, or a formula that returns "", also matches, so keep that column to real dates or truly empty cells.

=COUNTIFS($C$2:$C$15,"<="&$H$2,$D$2:$D$15,"")+COUNTIFS($C$2:$C$15,"<="&$H$2,$D$2:$D$15,">"&$H$2)

Result: 9

9: the people on the books on October 1.

Step 3: Leavers and the turnover rate

Leavers are the leave dates between the start and end of the period. COUNTIFS with two conditions on the same column does that: on or after Oct 1, and on or before Oct 31. October has 2: Ben and Ivy. That's the H6 helper cell.

Divide by the average of the start and end headcount, (9 + 9) / 2 = 9. Using the average is one common definition. Some teams divide by headcount at the start only, or by an average of every month-end. All are fine as long as you say which one you use and stick with it.

Don't divide by the number of names on the roster. That list includes everyone who ever worked there, so it gets bigger every month and makes turnover look lower than it is: 2 / 14 is 14.3%.

=H6/AVERAGE(H4,H5)

Result: 0.22222222222 shows as 22.2% with Percent format

About 0.222: 2 leavers against an average headcount of 9, so 22.2% for October.

Step 4: Yearly turnover without the x12 trap

It's tempting to multiply one month by 12. On a small team that goes badly wrong: 22.2% x 12 is 266.7%, which would mean the whole team left almost three times over. One bad month says little about the year.

For the year to date, count the leavers from Jan 1 to the end date and divide by the average of the Jan 1 and current headcount. Here that's 5 leavers and (8 + 9) / 2 = 8.5, so 58.8%. If you track every month, adding up the twelve monthly rates is another common way to get an annual figure.

Small teams swing a lot. With 9 people, every leaver moves the rate by about 11 points. Look at a rolling 12 months before you worry about a single month.

=COUNTIFS(D2:D15,">="&DATE(2026,1,1),D2:D15,"<="&H3)/AVERAGE(COUNTIFS($C$2:$C$15,"<="&DATE(2026,1,1),$D$2:$D$15,"")+COUNTIFS($C$2:$C$15,"<="&DATE(2026,1,1),$D$2:$D$15,">"&DATE(2026,1,1)),H5)

Result: 0.58823529412 shows as 58.8% with Percent format

About 0.588: 5 leavers this year against an average headcount of 8.5.

Step 5: Voluntary vs involuntary, by department, and new hires

Add a third condition to the leavers count to split it. =COUNTIFS(D2:D15,">="&H2,D2:D15,"<="&H3,E2:E15,"Voluntary") gives 1 for October (Ben), so voluntary turnover is 1 / 9 = 11.1%. Swap in B2:B15,"Ops" for a department: Ops lost 1 person (Ivy) and had 2 people on Oct 1, so its rate is 50%. That's the small-number problem again, which is why department rates need a longer period.

New-hire turnover asks a sharper question: of the people we hired, how many left within 90 days? SUMPRODUCT checks four things per row: hired in July or August, has a leave date, and the leave date is at most 90 days after the hire date. Jon left after 60 days and Dev after 39, so 2 of the 4 summer hires left early.

Only measure a group whose 90 days are over. Hires from last week haven't had the chance to leave yet, so including them makes the number look better than it is.

=SUMPRODUCT((C2:C15>=DATE(2026,7,1))*(C2:C15<=DATE(2026,8,31))*(D2:D15<>"")*(D2:D15-C2:C15<=90))/COUNTIFS(C2:C15,">="&DATE(2026,7,1),C2:C15,"<="&DATE(2026,8,31))

Result: 0.5 shows as 50% with Percent format

0.5: 2 of the 4 people hired in July and August left within 90 days.

Common mistakes

Deleting rows when people leave

Keep leavers on the roster with a leave date. Without them you can't count leavers or past headcount.

Dividing by everyone on the roster

The roster includes past staff, so the rate shrinks over time. Divide by the average headcount for the period.

Multiplying one month by 12

One month on a small team can give 200%+. Use the year's leavers over the year's average headcount, or add up the monthly rates.

Leave dates stored as text

Dates typed as text don't match ">="&H2. Check with =ISNUMBER(D2) and fix them with Data > Text to Columns or DATEVALUE.

FAQ

What is the formula for employee turnover rate?

Leavers in the period divided by the average number of employees in that period, times 100 for a percentage. In Excel: =leavers/AVERAGE(start_headcount,end_headcount), formatted as %.

Do new hires who leave in the same month count?

In this method, yes. They were employed, then left inside the period, so they count as a leaver. They aren't in the start headcount, so a month with lots of short stays shows higher turnover. That's usually what you want to see.

Should I count retirements and contract ends?

That's your call, but decide once and write it next to the numbers. Many teams report total turnover and then voluntary turnover separately, because voluntary is the one you can act on.

How do I calculate retention rate in Excel?

One simple version is the people on the books at the start who are still there at the end, divided by the start headcount: =COUNTIFS(C2:C15,"<="&H2,D2:D15,"")/H4 counts those still employed today, 7 of 9 (77.8%) here. It ignores new hires, so it won't be 100% minus turnover.

Functions used

Related guides

Updated