Attrition dashboard in Excel: rates and cohort checks

An attrition dashboard in Excel should show exits, average headcount and an explicitly defined exit rate for the same month and workforce. Start with aggregate monthly counts by team, reconcile the headcount movement, and calculate rates from the checked totals. Use the dashboard to identify where staffing needs investigation and planning.

Define the population before building the workbook

This example covers employees of one US field-services company, with sales and operations teams. Contractors are excluded from every count. A company exit includes voluntary resignations and other departures; an internal transfer changes a team but does not count as a company exit.

Call the metric monthly total-exit attrition and put that definition beside the dashboard. Keep a separate voluntary rate so a planned reduction does not look like a sudden increase in resignations. Retirement and involuntary departures belong in the example's other-exit column, with category details retained by the workforce reporting owner.

Use one row per month and team. Obtain approved aggregate counts from workforce reporting, together with opening and closing snapshots. A period's opening headcount must equal the preceding period's closing headcount for the same team boundary, unless a documented restatement explains the difference.

Set up the input rows and reconciliation

Put the following data in columns A:H on a worksheet named Inputs, with headings in row 1. Store month as a real date, using July 1, August 1 and September 1, 2026, displayed as month names. Transfers are positive for arrivals and negative for departures from the team.

The table occupies A:H. Reserve I for the reconciliation difference, J for total exits, K for average headcount and L for the total-exit rate. Enter these formulas in row 2 and fill through row 7:

I2 = C2+D2-E2-F2+G2-H2
J2 = E2+F2
K2 = (C2+H2)/2
L2 = IF(K2=0,NA(),J2/K2)

Every I cell must equal zero. July sales gives 100 + 6 - 3 - 1 - 2 - 100 = 0. July operations gives 150 + 8 - 4 - 2 + 2 - 154 = 0. The transfers cancel at company level, and closing headcount becomes the next month's opening headcount.

The opening-and-closing average is an approximation of workforce exposure. If hiring or departures are heavily concentrated within a month, use a consistently calculated daily average instead and document the change. Do not switch denominator methods quietly.

Calculate checked monthly company rates

Create a Dashboard summary with month in A2:A4. Columns B:F are opening headcount, closing headcount, exits, average headcount and attrition rate. Use real month-start dates that match the input dates.

B2 = SUMIFS(Inputs!$C$2:$C$7,Inputs!$A$2:$A$7,A2)
C2 = SUMIFS(Inputs!$H$2:$H$7,Inputs!$A$2:$A$7,A2)
D2 = SUMIFS(Inputs!$J$2:$J$7,Inputs!$A$2:$A$7,A2)
E2 = (B2+C2)/2
F2 = IF(E2=0,NA(),D2/E2)

Fill down through row 4. Microsoft's SUMIFS documentation explains conditional summing; the month criterion here selects matching team rows.

Percentages are rounded to two decimal places for display; calculations retain full precision. July is 10 / 252 = 3.97%. It is not the simple average of the two team rates because the teams have different headcounts.

If you also report the quarter, define its denominator as the average of these monthly average headcounts. Exits are 10 + 9 + 8 = 27; average headcount is (252 + 255 + 258.5) / 3 = 255.1667, shown to four decimal places. The quarterly exit rate is 27 / 255.1667 = 10.58%, calculated from the unrounded denominator. Label this as a quarterly exit rate rather than a probability that an opening employee left.

Build a dashboard that preserves the denominator

Place the monthly rate trend beside the exit counts and average headcount. Use a line chart for the monthly percentage and a separate column chart for counts. Keep axes and units visible, and include the reporting period and population definition in the title area.

Add a team comparison table showing exits, average headcount and rate together. Use the existing PivotTable guide for the detailed build. Microsoft describes PivotTables and PivotCharts as ways to summarize and present worksheet data. Sum exit and headcount measures, then calculate the ratio; do not summarize the rate field with an average. Covirage is not affiliated with Microsoft.

For a tenure view, request aggregate rows with mutually exclusive tenure bands and a consistent assignment rule. Follow attrition by tenure for the measure's purpose. A band-level rate needs the headcount exposed within that same band, not total company headcount.

The workforce reporting owner approves definitions and reconciliations. Team leaders investigate operational context, while finance uses the checked headcount movement in the ramp and attrition capacity plan. Show a reporting cutoff and flag incomplete months so a partial month is not compared with a closed one.

Where it goes wrong

• Changing the exit definition. Mixing resignations with all departures across months changes the meaning of the trend.
• Averaging team percentages. Aggregate exits and average headcount before dividing to obtain the company rate.
• Counting transfers as company departures. Transfers affect team movement; their company net should reconcile to zero within the stated boundary.
• Mixing people and positions. Vacancies, approved roles, full-time equivalents and employee headcount answer different questions.
• Reading too much into small groups. Show the underlying counts, restrict sensitive breakdowns, and investigate context before attributing a change to a cause.

Compare your workforce file in Covirage

Bring an aggregate workbook of monthly headcount, hires, exits and transfers; Covirage maps the columns, and its tools compute and check the rates and workforce movements. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See capacity planning with Covirage for the related business use.