How to calculate employee turnover rate in Excel
Formula (Excel and Google Sheets)
=C2/AVERAGE(B2,D2)Divide leavers by the average headcount: =C2/AVERAGE(B2,D2), where B is the starting and D the ending headcount.
- Result on the example
- one result per row in
E2, filled down toE6 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2, filled down to E6. Change any input and the result updates, or edit the formula to experiment.
How it works
Headcount changes during the period, so the standard HR formula divides leavers by the average headcount: the start plus the end, halved. Sales lost 6 people against an average of 42: 14.3%.
=C2/AVERAGE(B2,D2)Result0.1428571429
C26The value in C2.
B240The value in B2.
D244The value in D2.
AVERAGE(B2,D2)42AVERAGE: Arithmetic mean of the numbers.- number1
B240 - number2
D244
- number1
C2/AVERAGE(B2,D2)0.1428571429The / operator divides.
In Google Sheets
The same formula works in Google Sheets: =C2/AVERAGE(B2,D2). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =C2/B2 | 15.0%, 20.0%, 5.6%…Different result | Divides by the starting headcount; simpler, but it overstates turnover in shrinking teams and understates it in growing ones. Gives Sales 15.0%. |
Common mistakes
- Monthly turnover rates can't be averaged into an annual rate. Add up the leavers for the year and divide by the average headcount for the year.
- Decide whether leavers include involuntary exits and internal transfers, and keep the rule the same across periods.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate employee turnover rate
E2, calculate the turnover rate: leavers divided by the average of the starting and ending headcount, then fill down to E6.Questions
How do I calculate the company-wide rate?
Use the totals: =SUM(C2:C6)/AVERAGE(SUM(B2:B6),SUM(D2:D6)).
How do I calculate the retention rate?
Count the people from the start still employed at the end, divided by the starting headcount; with these numbers, =(B2-C2)/B2 if no one who joined also left.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.