How to Track Employee Turnover Rate % in Excel for Better Workforce Planning

How to Build Turnover Rate % in Excel

This walkthrough shows how to organize the data, calculate monthly headcount and separations, build a 12 month rolling turnover view, and create the final chart in Excel.

1. Organize the monthly employee dataset

• Set up your data so each row represents an employee for a specific month.
• Include a Month End column so each employee is tied to the end of a specific month.
• Add a Status column to identify whether the employee is active.
• Add a Separation in the Month column and mark it Yes when an employee quit or was terminated.
• Once an employee separates, do not include them in future months.
• If you are maintaining a more detailed dataset, you can keep historical separation dates for reference, but for this KPI the key fields are the month, active status, and whether separation happened in that month.

2. Create a unique list of months

• Use the UNIQUE function on the Month End column.
• This creates the month list that will drive the summary table.
• If you only update this once per month, using month-end dates is enough.
• Place this list in the summary area where you want the KPI table and chart to be built.

3. Set up the summary table columns

Create the summary table with these columns:

• Month
• Active Head Count
• Separation
• 12 Month Rolling
• Target

You can type these labels manually or copy and paste them into place.

4. Count active headcount by month

• Use the COUNTIFS function to count employees whose status is marked Active.
• In the same function, match that count to each Month End value in your summary table.
• Drag the formula down so Excel calculates active headcount for every month in the list.

This gives you the monthly active headcount at the end of each month.

5. Count separations by month

• Use another COUNTIFS function for the Separation column.
• Count rows where Separation in the Month is marked Yes.
• Also match the calculation to the corresponding Month End value.
• Drag the formula down through the full month list.

This gives you the total number of employees who quit or were terminated in each month.

6. Build the 12 month rolling turnover column

• Start the rolling calculation once you have enough months available.
• Use the SUM function to total separations across the past 12 months.
• Use the AVERAGE function to calculate the average active headcount across that same 12 month period.
• Then divide those two results to get the rolling percentage.
• Copy the calculation down for the remaining months.
• Format the results as a percentage using Ctrl + Shift + 5.

This creates the rolling turnover view used in the chart.

7. Add a target line

• Create a Target column in the summary table.
• Enter the same target percentage for each month.
• In the video, a 20% target line is used across the chart.
• Fill the target down so it aligns with each month in the reporting range.

This gives you a benchmark to compare against the rolling turnover trend.

8. Select the chart data

To create the visual:

• Highlight the Month column
• Highlight the Separation column
• Hold Ctrl and also highlight the 12 Month Rolling column
• Highlight the Target column

This lets you chart monthly separations alongside the rolling turnover percentage and the target line.

9. Insert and configure the combo chart

• Go to Insert
• Open Recommended Charts
• Then switch to All Charts
• Choose Combo

Set the series like this:

• Separation as a column
• 12 Month Rolling as a line
• Target as a line

Place both percentage-based series on the Secondary Axis so they are not plotted on the same scale as the separation count.

10. Clean up the chart formatting

To make the chart easier to read:

• Make the target line thinner
• Change the target line color if needed
• Add markers only if they help visibility
• Add data labels to the percentage line if you want to emphasize the rolling rate
• Optionally add a light background to the labels so they stand out
• Rename the chart title
• Format the percentage axis as Percentage
• Remove decimals if you want a cleaner display

This turns the raw chart into a cleaner monthly turnover dashboard.

11. Finalize the visual

• Reposition labels if any overlap
• Keep only the labels that add clarity
• Make sure the right axis displays percentages instead of decimals
• Check that the chart clearly separates monthly separation counts from the rolling turnover trend and target line

The finished result is a chart that shows monthly separations, the 12 month rolling turnover rate, and a target benchmark in one view.