Employee Turnover Rate Formula in Excel: Monthly, Annualized, and a Free Template

The employee turnover rate formula fits in one line. Building a spreadsheet that computes it correctly every month, rolls it up to an annual figure, and doesn't quietly lie to you when headcount changes takes a bit more care. This is the hands-on version: the exact column layout, the exact Excel and Google Sheets formulas, the mistakes that break the number, and a small dashboard so the rate is something people actually look at.

If you want the concept first — what counts as a separation, why the denominator is an average, why voluntary and involuntary tell different stories — read how to calculate employee turnover rate. This post assumes you know what the rate means and just want the sheet to work. And if you'd rather skip the spreadsheet entirely, our free employee turnover rate calculator runs the same math in your browser.

The Turnover Rate Formula in One Line

Turnover rate = Separations ÷ Average headcount × 100, where Average headcount = (Headcount at start + Headcount at end) ÷ 2

Everything in the spreadsheet below is a way of computing that one line reliably. Two things the formula quietly requires: the separations and the headcount must cover the same window, and the denominator must be an average, not a snapshot. Both are easy to get right when a formula does it for you and easy to get wrong when a person does.

The Monthly Tracker Layout

Eight columns is enough. Here is the layout with a year of example numbers for a 100-person company. Column letters are shown so the formulas in the next section line up exactly.

Twelve months, 15 total separations (11 voluntary, 4 involuntary), 18 hires, and headcount that drifts from 100 to 103. The monthly rate wobbles between 0% and 2% — which is the first lesson of monthly turnover data: individual months are noisy, and the useful number is the rolling one.

Only three columns are typed by hand each month: hires (C), voluntary separations (D), and involuntary separations (E). Everything else is a formula. Start headcount in row 2 is a typed seed value; from row 3 down it references the previous row's end.

The Exact Formulas

All of these work identically in Excel and Google Sheets. Row 2 is January; copy each formula down.

Headcount end and start

End headcount is start plus hires minus all separations. Next month's start is this month's end.

• F2 (headcount end): =B2+C2-D2-E2
• B3 (next month's start): =F2

Chaining start to the prior end means the sheet reconciles itself: if your HRIS says December ended at 103 and the sheet says 104, a separation got missed somewhere and you know to go find it.

Average headcount

• G2: =(B2+F2)/2

Monthly turnover rate

Total separations divided by average headcount. Format the column as a percentage so you don't need to multiply by 100 in the formula.

• H2 (overall monthly rate): =(D2+E2)/G2
• Or written out without the helper column: =(D2+E2)/((B2+F2)/2)

Voluntary-only rate

Add a column I and filter the numerator to just the resignations. This is the one most teams should treat as their headline number, since it's the part you can influence.

• I2 (voluntary monthly rate): =D2/G2

Rolling 12-month rate

This is the number that belongs on the dashboard. Sum the last twelve months of separations and divide by the average of the last twelve months' average headcounts. In row 13 (December, once you have a full year):

• J13 (rolling 12-month rate): =SUM(D2:E13)/AVERAGE(G2:G13)
• J13 voluntary-only: =SUM(D2:D13)/AVERAGE(G2:G13)

Copy it down and the ranges slide with it — row 14 covers February through January, and so on. For our example: 15 separations ÷ 101.0 average headcount = 14.8% for the year, with 10.9% of that voluntary.

Annualized from a single month

Before you have a full year of data, the quick approximation is to multiply the monthly rate by twelve:

• Simple annualization: =H2*12

Use it sparingly. It assumes the month is typical, and it usually isn't — February above annualizes to 24%, December to 0%, and neither is true. Summing twelve months over the window's average headcount is more accurate for two reasons: it uses actual separations rather than extrapolating one month, and it divides by an average across the window rather than a single month's. Once you have twelve rows, retire the multiplication.

Year-to-date rate

Separations so far this year divided by the average headcount so far. Anchor the top of the range with $ so it stays fixed as you copy down:

• K2 (YTD rate): =SUM($D$2:E2)/AVERAGE($G$2:G2)

In March that reads =SUM($D$2:E4)/AVERAGE($G$2:G4) — three months of separations over three months of average headcount. Note the YTD figure is not annualized: in the example, March's YTD rate is 5.0% (5 separations over a 100.5 average), which is on pace for roughly 20% for the year — a hot first quarter that the rolling rate later smooths out to 14.8%.

Common Mistakes the Spreadsheet Can Catch

• Counting transfers as separations. If your HRIS export logs an internal move as a termination plus a hire, it inflates both C and D. Filter the export on "left the company" before you type numbers in, or add a "Transfers" column that the end-headcount formula ignores.
• Dividing by end-of-year headcount. The formula uses AVERAGE(G2:G13), not F13. In a company that grew from 100 to 130, dividing by 130 instead of the 115 average understates the rate by more than 10%. The helper column exists precisely so nobody is tempted to shortcut this.
• Mixing contractors in. Contract endings are not turnover. If contractors sit in the same HRIS, keep a separate tracker or exclude them at export time — a 12-month contract that ends on schedule will otherwise show up as a resignation.
• Annualizing a single noisy month. Covered above, but it's the mistake most likely to reach a leadership deck. One bad month times twelve is a headline, not a rate.

A Mini Dashboard in Ten Minutes

A tracker nobody opens is a tracker that doesn't exist. Three additions make the sheet glanceable.

Conditional formatting thresholds

Select the rolling 12-month column and add a color scale, or three explicit rules. In Excel: Home → Conditional Formatting → Highlight Cells Rules → Greater Than. In Sheets: Format → Conditional formatting → Format cells if → Greater than. Pick the thresholds from your own history — amber above last year's rate, red at last year's rate plus 5 points — rather than an internet benchmark; the goal is to make movement visible.

A sparkline

One cell, twelve months of shape. In Excel: Insert → Sparklines → Line, data range H2:H13. In Google Sheets it's a formula: =SPARKLINE(H2:H13, {"charttype","line"}). Put it at the top of the sheet next to the current rolling rate and you have a one-glance summary.

A line chart

Select columns A, H, and J (month, monthly rate, rolling rate) and insert a line chart — Insert → Chart in both tools. The monthly line will look like a saw blade and the rolling line like a trend; that contrast is the whole argument for the rolling formula. Add the voluntary-only rolling series as a third line if you track it.

What "Good" Looks Like

There is no universal good turnover rate. Rates vary enormously by industry — the same figure can be alarming in one sector and enviable in another — so compare against your own trend line first and your industry second. We've collected the published benchmarks in employee turnover rates by industry. The more actionable split is voluntary versus involuntary: the Work Institute estimates about 3 in 4 voluntary departures are preventable, so a rising voluntary line is the one worth a meeting.

Turning the Rate Into a Cost

A percentage rarely moves a budget. Multiply it out and it does. Add a small block at the top of the sheet with average salary and a replacement-cost multiplier, then:

• Annual turnover cost: =AVERAGE(G2:G13)*J13*Salary*Multiplier

For the multiplier, the research gives you a range: the Work Institute puts replacement at roughly 33% of salary at the conservative end, and SHRM estimates 50–60% once hiring and onboarding are counted. Using our canonical example — 100 employees, $65,000 average salary, 15% turnover, 50% replacement cost — that's 15 departures × $65,000 × 50% = $487,500 a year. Swap in 33% and it's about $322,000; the point is that even the conservative figure is a real line item.

If you'd rather not build that block yourself, our employee turnover cost calculator takes headcount, salary, rate, and multiplier and returns the figure instantly, and the turnover rate calculator handles the rate itself. Both are free and neither asks for an email.

Making the Line Go Down

The spreadsheet tells you where you are. Moving the number is a different job, and recognition is one of the cheapest, best-documented levers for the voluntary half of it: Deloitte's research links strong recognition cultures to up to 31% lower voluntary turnover. Applied to the example above, that's roughly $150,000 a year recovered. We worked through that arithmetic in recognition vs. turnover: the retention math.

There's also a leading-indicator angle. Turnover shows up in this sheet months after the decision to leave was made; recognition activity drops off well before that. If you want a signal that moves earlier than column D, recognition as an early warning system for attrition explains what to watch. (Yes, Propsly is ours — the tracker above works with or without it.)