Headcount tracking is something HR teams often do in five different spreadsheets that slowly drift out of sync. This guide shows you how to build one clean headcount tracker in Excel that stays up to date, breaks down headcount by department and location, and makes it easy to answer questions like "how many engineers do we have in London?" without digging through old files.
The Employee Master List
Everything starts from one sheet: the employee master list. One row per employee, with columns for: employee ID, name, department, location, role, employment type (full-time, part-time, contractor), start date, end date (blank if still active), and status (Active, On Leave, Terminated).
Keep this as your single source of truth. Every summary and report in your workbook pulls from this sheet. Don't duplicate data anywhere else.
Active Headcount by Department
On a new sheet, build a summary table with COUNTIFS to count active employees by department:
=COUNTIFS(EmployeeList!C:C, A2, EmployeeList!I:I, "Active")
Where C is the Department column, A2 is the department name in your summary table, and I is the Status column. Copy this for every department. The result: active headcount per department, always current.
Headcount by Department and Location
For a two-dimension breakdown, add a second COUNTIFS:
=COUNTIFS(EmployeeList!C:C, A2, EmployeeList!D:D, B1, EmployeeList!I:I, "Active")
Where A2 is the department (rows) and B1 is the location (columns). Set this up as a grid with departments in rows and locations across columns. This gives you a headcount matrix you can read across to find department-by-location counts instantly.
Tracking Headcount Over Time
Add a Snapshot sheet where you record total headcount on the first of each month. Use a formula to pull the current count:
=COUNTIF(EmployeeList!I:I, "Active")
Copy this value as a paste-special (values only) into your snapshot table each month so it becomes a historical record. Plot this as a line chart to visualize headcount growth or contraction over time.
Tracking New Hires and Departures
Count employees who started in a given month:
=COUNTIFS(EmployeeList!E:E, ">="&DATE(2026,4,1), EmployeeList!E:E, "<="&DATE(2026,4,30))
Count employees who left in a given month:
=COUNTIFS(EmployeeList!F:F, ">="&DATE(2026,4,1), EmployeeList!F:F, "<="&DATE(2026,4,30))
Add these to your monthly snapshot alongside total headcount. Net change is new hires minus departures.
Flagging Open Roles
If you also track open positions in your master list (with a status of "Open"), your headcount tracker can show both filled and unfilled positions by department. Add an Open Roles column to your summary table:
=COUNTIFS(EmployeeList!C:C, A2, EmployeeList!I:I, "Open")
This gives HR and finance a complete picture: current headcount plus planned headcount.
The Easy Way: Using SheetXAI in Excel
Example 1: Your employee list is already in the spreadsheet.
"I have an employee master list on Sheet 1 with columns for name, department, location, start date, end date, and status. Build a headcount summary showing active employees by department and location, monthly new hires and departures, and a headcount trend chart."
SheetXAI reads your data, writes the COUNTIFS formulas, builds the department-by-location matrix, and generates the trend chart.
Example 2: Your HR data lives in your HRIS.
"Pull employee data from our HRIS and build a headcount tracker showing active headcount by department and location, with monthly hire and departure counts."
SheetXAI connects to the data source, pulls the records, and builds the full tracker.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a Hiring Pipeline Tracker in Google Sheets, How to Use COUNTIFS in Excel, and Google Sheets AI Guide.
