Track Voluntary Turnover % in Excel and
Identify Retention Trends

How to Calculate Voluntary Turnover % in Excel

1. Create a Unique List of Months

• Start by building a list of reporting months from the end-of-month date column.
• Use the UNIQUE function to pull each month only once.
• This gives you the monthly structure needed for the rest of the analysis.

2. Count Active Employees by Month

• Create a column for active employees.
• Use the COUNTIFS function because the count needs more than one condition:
  • the employee must be marked as active at the end of the month
  • the record must match the month in your summary table
• Fill the formula down for all months in the list.

This gives you the end-of-month active employee count for each reporting period.

3. Count Voluntary Separations by Month

• Create another column for voluntary separations.
• Use COUNTIFS again so the count matches:
  • records labeled as voluntary
  • the same reporting month in your summary table
• Fill the formula down through the full list of months.

This produces the number of voluntary exits for each month.

4. Build the 12-Month Moving Calculation

• Add a new column for the 12-month moving result.
• Use the SUM function to total the last 12 months of voluntary separations.
• Use the AVERAGE function to calculate the average active employee count across those same 12 months.
• Then divide the rolling separations total by the rolling average headcount.
• Format the result as a percentage.

Once you copy the calculation down, Excel will keep moving the 12-month window forward month by month.

5. Fill the Rolling Calculation Down the Table

• Double-click the fill handle or drag the formula down.
• As the formula moves, the 12-month range updates automatically.
• The first rows will not have a full 12-month history yet, so the rolling result begins only once enough monthly data exists.

This creates the moving trend used for the KPI chart.

6. Select the Data for the Chart

• Highlight the month column.
• Highlight the voluntary separation counts.
• Highlight the 12-month moving percentage column.
• These fields will be used to build the visual.

This setup lets you compare raw monthly exits with the longer-term turnover trend.

7. Insert a Combo Chart

1. Go to Insert.
2. Choose Recommended Charts.
3. Select a Combo Chart.
4. Keep the monthly separation values as columns.
5. Set the turnover percentage series as a line.
6. Place the percentage line on a Secondary Axis.

The secondary axis is important because the turnover percentage is much smaller in scale than the monthly separation counts.

8. Adjust the Chart Range So It Starts Where the Rolling Data Begins

• Click the column series and drag the chart range down so it starts where the 12-month moving data begins.
• Do the same for the line series.
• This removes the empty or incomplete portion at the beginning of the chart and keeps the visual focused on the usable KPI period.

9. Add Data Labels If Needed

• Click the chart and turn on Data Labels if you want to show values.
• If the full chart becomes too crowded, apply labels only to the line series.
• To make labels easier to read, add a fill background behind the label text.

This is especially useful when the line values are small and harder to see.

10. Finalize the KPI View

• Clean up the chart layout so the columns and line are easy to read together.
• Keep the monthly separations on one axis and the turnover trend on the other.
• Review the finished chart to make sure the moving percentage is being displayed clearly across the correct date range.

At this point, the KPI report is complete and shows both monthly voluntary separations and the rolling voluntary turnover trend over time.