HR Analytics in Excel: Headcount, Turnover and Tenure

HR analytics in Excel means turning an employee list into the numbers leaders ask for: how many people work here, how many joined and left, what the turnover rate is and how long people stay. You can answer all of these with COUNTIFS and two date functions on one well-built table. This guide walks through each metric on a small roster, so you can build your own report or prepare for an HR or people-analytics Excel test.

What is HR analytics in Excel?#

HR analytics is measuring the workforce with data, and in Excel it usually means a handful of core metrics built from one employee table:

These are the same skills that appear in HR job tests. The guide to Excel skills for HR jobs covers the wider role, including payroll totals and eligibility flags.

Set up the employee data#

Every metric is easy when the data is one row per employee, with real dates and no merged cells. Here is a small roster in A1:E9:

A few rules keep the analysis reliable:

• Dates must be real dates, not text. A real date lines up on the right of the cell by default.
• Leave the Leave cell blank for current staff. Don't type "n/a" or 0.
• Spell departments the same way every time. "Sales" and "sales " (with a space) count as different values. A drop-down list prevents this.
• Use a fixed as-of date in a cell, such as 30 Sep 2025 in H1, so the report doesn't change every day.

Headcount with COUNTIFS#

Headcount is a count of active employees, and COUNTIFS adds as many conditions as you need. Total active staff:

=COUNTIF(E2:E9,"Active")

The result is 6. Active staff in Sales needs two conditions:

=COUNTIFS(B2:B9,"Sales",E2:E9,"Active")

The result is 3 (Aisha, Dmitri and Hiro). Ben is in Sales but has left. Put the department names in a column and point the criteria at them, with the ranges locked, to build a full table: =COUNTIFS($B$2:$B$9,G5,$E$2:$E$9,"Active"). Divide each count by the total for the department mix: Sales is 3 / 6 = 50%.

Headcount on a past date#

A status column only shows today. For headcount on a date in G1, count people who had started by then and either hadn't left or left after it:

=COUNTIFS(C2:C9,"<="&G1,D2:D9,"")+COUNTIFS(C2:C9,"<="&G1,D2:D9,">"&G1)

With G1 set to 1 Jan 2025, the result is 7: five current staff who had started, plus Ben and Elena, who were still there. Grace is left out because she started on 13 Jan. The "" criteria matches blank leave dates. The & joins each operator to the date in G1. The comparison of COUNTIF vs COUNTIFS explains why one function handles one condition and the other handles many.

Hires, leavers and turnover rate#

Hires and leavers are date-range counts. Leavers in 2025:

=COUNTIFS(D2:D9,">="&DATE(2025,1,1),D2:D9,"<="&DATE(2025,12,31))

The result is 2 (Ben and Elena). Swap the leave column for the start column, C2:C9, to count hires in 2025: 1 (Grace).

A common turnover rate is leavers in the period divided by average headcount. Headcount was 7 on 1 Jan 2025 and 6 on 30 Sep 2025, so the average is 6.5:

=2/AVERAGE(7,6)

The result is 0.308, or 30.8% when formatted as a percentage. In a real report, point at the cells that hold the counts instead of typing numbers. Organisations define turnover differently: some use monthly averages, some exclude fixed-term contracts or count only voluntary leavers. Write your definition next to the number so readers can compare like with like.

Tenure with DATEDIF and YEARFRAC#

Tenure is the time from the start date to either the leave date or the as-of date. With the as-of date in H1, whole years for the first employee are:

=DATEDIF(C2,IF(D2="",$H$1,D2),"Y")

The IF picks the leave date when there is one and the as-of date otherwise. Aisha has 6 complete years at 30 Sep 2025, and Ben had 2 when he left. Add DATEDIF(...,"YM") for the extra months. The DATEDIF function guide lists every unit.

For an average, decimal years are more accurate. In a Tenure column F, use =ROUND(YEARFRAC(C2,IF(D2="",$H$1,D2),1),2) and fill down. Then average the active staff only:

=AVERAGEIFS(F2:F9,E2:E9,"Active")

The result is 4.77 years. The days between dates lesson practises DATEDIF and tenure formulas step by step.

Build a simple HR dashboard#

A dashboard is a small summary table that updates when the roster changes, plus one or two charts. Lay it out like this:

1. Inputs: the as-of date and the period start and end dates in labelled cells.
2. Headline numbers: headcount, hires, leavers, turnover rate and average tenure, each one formula pointing at the inputs.
3. By department: department names down a column, with COUNTIFS headcount, leavers and AVERAGEIFS tenure beside them.
4. Charts: a bar chart of headcount by department, and a line of monthly headcount if you track it over time.

A PivotTable is a fast way to explore the same roster, but formulas keep the layout fixed for a monthly pack. The COUNTIFS function guide is a handy reference while you build it. The same counting skills serve other people-heavy roles too; see Excel for healthcare admin, and for checking HR data for errors, Excel for auditors.

HR analytics questions in Excel tests#

HR and people-analytics Excel tests usually give you a roster and ask for exactly these metrics under time pressure: look up a salary, count by department, total by team, calculate tenure, flag a missing ID. The HR roster interview drill works through that format. For a broader timed test, the data analyst Excel test guide explains what to expect, and the data analyst mock test lets you practise against the clock.

Practise it#

Try the COUNTIFS exercise on this page, then practise COUNTIFS and SUMIFS in a live spreadsheet, where you type each formula and it is checked instantly. Finish with the HR roster interview drill to put headcount, tenure and lookups together.