Headcount is one of the most frequently requested HR metrics, yet producing a reliable headcount report is not always as straightforward as it appears.
An employee may have joined or left during the reporting period. Someone may work part-time, move between departments or appear more than once in the source data. Contractors, employees on leave and vacant positions may also be treated differently across organisations.
As a result, two reports described as “headcount” can produce different answers even when they use the same HR system.
A useful headcount report therefore requires more than a simple count of spreadsheet rows. It needs a clear definition, structured employee data and a repeatable calculation method.
This guide explains how to create a practical headcount report in Excel that can be refreshed each month and used for management reporting, workforce planning and People Analytics.
What Is Headcount in HR?
Headcount normally refers to the number of individual employees working for an organisation at a particular point in time.
Each employee is counted as one person, regardless of whether they work full-time or part-time.
For example:
- One full-time employee equals one headcount.
- One part-time employee also equals one headcount.
- Two part-time employees equal two headcount.
This differs from full-time equivalent, usually abbreviated to FTE. FTE reflects working capacity rather than the number of people.
An employee working half of the organisation’s standard hours might represent:
- Headcount: 1
- FTE: 0.5
Both measures are useful, but they answer different questions.
Headcount tells you how many people are employed. FTE tells you how much workforce capacity those employees represent.
Define Headcount Before Building the Report
Before opening Excel, agree on what should be included in the calculation.
Questions to clarify include:
- Are permanent and fixed-term employees included?
- Are contractors included?
- Are employees on maternity, parental or long-term sick leave included?
- Are interns and apprentices included?
- Should employees leaving on the reporting date still be counted?
- Are employees counted according to their legal employer or current department?
- Is the figure based on the last day of the month or an average across the month?
There is no single definition that is correct for every organisation. The important point is that the definition should be documented and applied consistently.
A short note at the top of the report can prevent confusion:
Headcount includes all active permanent and fixed-term employees as of the final calendar day of the month. Contractors and vacant positions are excluded.
This gives managers confidence that they understand what the number represents.
What Data Do You Need?
A basic headcount report normally requires one row per employee and the following fields:
- Employee ID
- Employee name
- Start date
- End date
- Employment status
- Department
- Business unit
- Location
- Job title
- Employment type
- FTE value
- Manager
Employee ID is particularly important. Names are not always unique, and the same employee may appear under different versions of their name.
Where possible, export the data directly from your HR system rather than copying it manually into the reporting workbook.
Step 1: Convert the Employee Data into an Excel Table
Place the source data on a worksheet called Employee Data.
Click anywhere inside the data and press:
Ctrl + T
Confirm that the table contains headers.
Using an Excel Table provides several advantages:
- New rows are included automatically.
- Formulas extend to new records.
- Filters are added to every column.
- PivotTables and charts are easier to refresh.
- Structured formulas are easier to understand.
Give the table a meaningful name such as:
EmployeeData
You can change the name under Table Design → Table Name.
Avoid leaving blank rows, merged cells or manually inserted totals inside the source table. The data should remain in a simple rectangular structure.
Step 2: Create a Reporting Date
On a separate worksheet called Headcount Report, enter the reporting date in cell B2.
For example:
31/07/2026
Label cell A2:
Reporting date
This date will control which employees are counted as active.
Using a reporting-date cell is better than embedding a fixed date inside every formula. When the next reporting period begins, you only need to change one value.
Step 3: Identify Active Employees
An employee is generally active on the reporting date when:
- Their start date is on or before the reporting date.
- Their end date is blank or on or after the reporting date.
Add a column called Active at Reporting Date to the EmployeeData table.
Use the following formula:
=--AND([@[Start Date]]<='Headcount Report'!$B$2,OR([@[End Date]]="",[@[End Date]]>='Headcount Report'!$B$2))
The formula returns:
- 1 when the employee is active.
- 0 when the employee is not active.
The double minus at the beginning converts TRUE and FALSE into 1 and 0, making the result easier to sum and analyse.
Before relying on the formula, confirm how your organisation treats an employee whose final working date is the reporting date. Some organisations include that employee; others treat them as no longer active.
Step 4: Calculate Total Headcount
Once the active flag has been created, total headcount can be calculated with:
=SUM(EmployeeData[Active at Reporting Date])
This works when every employee appears only once in the table.
Where an employee can appear on multiple rows, perhaps because of several contracts or assignments, a simple sum may count the same person more than once. In that case, use Employee ID to create a distinct count through a PivotTable or the Excel Data Model.
An Alternative Formula Without an Active Column
You can also calculate headcount directly with COUNTIFS:
=COUNTIFS(EmployeeData[Start Date],"<="&$B$2,EmployeeData[End Date],">="&$B$2)+COUNTIFS(EmployeeData[Start Date],"<="&$B$2,EmployeeData[End Date],"")
The first part counts employees whose end date is on or after the reporting date.
The second part counts employees whose end date is blank.
This method is useful for a quick calculation, although an active-status column is often easier to audit and reuse in other parts of the report.
Step 5: Create Headcount by Department
Managers rarely need only the total workforce number. They usually want to understand how headcount is distributed.
Create a PivotTable from the EmployeeData table:
- Click inside the table.
- Select Insert → PivotTable.
- Place the PivotTable on the Headcount Report worksheet.
- Add Department to Rows.
- Add Active at Reporting Date to Values.
- Confirm that the value is summarised by Sum.
The result will show active headcount for each department.
You can use the same approach for:
- Business unit
- Location
- Employment type
- Job level
- Legal entity
- Manager
- Gender or another diversity dimension, where appropriate and permitted
Add slicers when managers need to filter the report interactively.
Step 6: Build a Monthly Headcount Trend
A single month-end figure provides only a snapshot. A monthly trend makes it easier to see whether the workforce is growing, shrinking or remaining stable.
Create a list of month-end dates in a separate table.
For example:
| Month | Reporting date |
|---|---|
| January 2026 | 31/01/2026 |
| February 2026 | 28/02/2026 |
| March 2026 | 31/03/2026 |
The next month-end date can be generated with:
=EOMONTH(B2,1)
To calculate headcount for each month-end date, use:
=COUNTIFS(EmployeeData[Start Date],"<="&B2,EmployeeData[End Date],">="&B2)+COUNTIFS(EmployeeData[Start Date],"<="&B2,EmployeeData[End Date],"")
Copy the formula down for each reporting month.
You can then insert a line chart to display the trend.
A headcount trend is usually more informative than a large table because it allows leaders to see immediately when workforce numbers changed.
Step 7: Calculate New Starters and Leavers
Headcount movement becomes easier to understand when it is shown alongside starters and leavers.
New starters during the month
Assuming the start and end of the reporting month are stored in cells B3 and B4:
=COUNTIFS(EmployeeData[Start Date],">="&$B$3,EmployeeData[Start Date],"<="&$B$4)
Leavers during the month
=COUNTIFS(EmployeeData[End Date],">="&$B$3,EmployeeData[End Date],"<="&$B$4)
These figures help explain why headcount changed.
For example:
- Opening headcount: 480
- New starters: 18
- Leavers: 11
- Closing headcount: 487
The calculation is:
480 + 18 - 11 = 487
If the numbers do not reconcile, investigate transfers, rehires, data corrections or differences in the reporting definition.
Step 8: Include FTE Alongside Headcount
Headcount alone can sometimes give an incomplete picture.
Suppose a department grows from ten to twelve employees, but the two new employees both work part-time. Headcount increases by two, while available workforce capacity increases by less.
Including both measures helps managers distinguish between the number of people and the amount of working capacity.
Your summary could show:
| Measure | Current month | Previous month | Change |
| Headcount | 487 | 480 | +7 |
| FTE | 451.6 | 446.2 | +5.4 |
Make sure that FTE values are maintained consistently in the source data. A full-time employee will normally have an FTE of 1, while part-time employees will have a proportionate value.
Step 9: Design a Clear Headcount Dashboard
A useful HR headcount dashboard does not need to contain dozens of charts.
A simple version could include:
Summary measures
- Current headcount
- Previous-month headcount
- Monthly change
- Current FTE
- New starters
- Leavers
Charts
- Monthly headcount trend
- Headcount by department
- Headcount by location
- Headcount by employment type
Filters
- Reporting period
- Business unit
- Department
- Location
- Employment type
Keep the most important information near the top. Avoid using several different charts to show the same measure.
Colour should highlight exceptions and changes rather than decorate the report.
Common Headcount Reporting Mistakes
Counting spreadsheet rows instead of employees
The number of rows may not equal the number of employees. Duplicate records, multiple assignments and historical contract rows can inflate the result.
Use a unique Employee ID and check for duplicates.
Mixing headcount and FTE
A person and their working capacity are not the same measure. Label each figure clearly.
Using today’s date accidentally
A report based on the current date will change whenever it is opened. Use a fixed reporting date so historical results can be reproduced.
Ignoring future starters
An employee record may already exist in the HR system even though the employee has not started. The start-date condition must be included.
Treating blank end dates inconsistently
A blank end date normally means the employee is still active, but this should be confirmed against the HR system’s data structure.
Overwriting previous reports
Saving a new spreadsheet over the previous month removes the audit trail. Keep a controlled historical table or automate the monthly snapshots.
Using inconsistent department names
Values such as “Human Resources,” “HR” and “People Team” may refer to the same department but appear separately in a PivotTable.
Standardise the data before reporting.
Use Power Query to Make the Process Repeatable
When the report is produced every month, manually cleaning and copying the data becomes inefficient.
Power Query can automate steps such as:
- Importing the latest HR system export
- Removing unnecessary columns
- Correcting data types
- Standardising department names
- Removing duplicate records
- Combining files from several months
- Creating calculated fields
- Refreshing the reporting table
Once the steps are created, the process can often be repeated by replacing the source file and selecting Refresh All.
This reduces manual effort and makes the reporting process more consistent.
When Should You Move the Report to Power BI?
Excel is often sufficient for a small or moderately sized headcount report, especially when it is maintained by one person and distributed to a limited audience.
Power BI becomes useful when:
- The report combines data from several systems.
- Managers need interactive filtering.
- Several reporting periods must be stored.
- The report is refreshed frequently.
- Access needs to be controlled.
- Different teams need different views.
- Manual spreadsheet distribution has become difficult.
The underlying reporting logic remains important regardless of the tool. Moving an unclear or unreliable headcount calculation into Power BI will not make it accurate.
Start by agreeing on the definitions and improving the source data. The visualisation tool comes afterwards.
What Makes a Headcount Report Useful?
An effective report should help managers answer practical questions:
- How many employees do we currently have?
- Where has headcount increased or decreased?
- Which departments are growing?
- How many people joined and left?
- How does headcount compare with FTE?
- Are actual workforce numbers aligned with the plan?
- Which changes require further investigation?
The report should therefore combine accurate calculations with a clear explanation of what changed and why it matters.
A technically correct spreadsheet is valuable. A report that supports a better workforce decision is more valuable.
Build Stronger Excel Skills for HR Reporting
PowerClick helps HR and L&D professionals develop practical Excel, Power BI and data skills using examples based on real workplace reporting challenges.
Explore Excel for HR to learn how to structure workforce data, create repeatable reports and turn HR information into clearer business insights.
For team training or support with an HR reporting challenge, contact PowerClick and tell us what you are trying to improve.
Frequently Asked Questions
What is the difference between headcount and FTE?
Headcount counts individual employees, with each person normally counted as one. FTE measures working capacity based on contracted hours. A part-time employee may count as one headcount but less than one FTE.
Should employees on leave be included in headcount?
They are commonly included when they remain employed, but the exact approach should follow your organisation’s reporting policy. The definition should be documented clearly.
What date should be used for a monthly headcount report?
Many organisations use the final calendar day of the month. Others report an average monthly headcount. Use the method required by your organisation and apply it consistently.
Can I create a headcount dashboard in Excel?
Yes. Excel Tables, PivotTables, PivotCharts, slicers and Power Query can be combined to create an effective and refreshable HR headcount dashboard.
Why does my HR system total differ from my Excel report?
Differences may result from reporting dates, employee-status definitions, duplicate rows, future starters, employees with multiple assignments or different treatment of leavers and employees on leave.
