How to build an accurate HR headcount report in Excel
Build headcount from employee-effective-date records, not raw row counts. Define who is active at each reporting date, resolve rehires and concurrent assignments, and calculate one approved person or position measure. Reconcile the result with payroll and explain every excluded or unresolved record.
Where the difficulty begins
HR exports often contain repeated employee IDs for job changes, leave events, or multiple assignments. COUNTROWS can therefore increase headcount when nobody joined and miss employees whose current status was overwritten.
From the field · HR analyst
Calculate month-end headcount across hires, exits, leave, and rehires
The HR system exports one row per employment event. A person hired in January, leaving in March, and rehired in May has three rows. Employees on parental leave remain employed, while contractors must be reported separately.
Columns used in the example
employee_id · effective_start · effective_end · event_type · employment_type · department · fte · payroll_status
Follow it with your own file
- Define active employee, contractor, leave, and FTE treatment with HR leadership.
- Construct valid employment intervals from effective-dated events.
- Evaluate each employee at every month-end and separate people from positions.
- Bridge monthly movement and compare the closing employee set with payroll.
Check: Each employee should be counted at most once in person headcount per date, intervals must not overlap unexpectedly, and opening plus hires minus exits plus approved corrections must equal closing headcount.
Check the source before you publish
- Population defined
- Effective dates validated
- Rehires handled
- People and FTE separated
- Movement bridge reconciled
Headcount is a time-specific business definition
A reliable HR headcount report states who, when, and what is counted. Person headcount, occupied positions, and FTE answer different questions, so the same employee history can produce several valid metrics only when each definition is explicit.
Where this usually goes wrong
Do not infer an exit from a blank payroll row or a missing monthly snapshot. Source delays and system transfers can create temporary gaps; keep them in an exception register until the employment timeline is confirmed.
Build the result step by step
- Define the measure: Choose people, positions, or full-time equivalents and document leave, contractors, and contingent workers.
- Build timelines: Use effective start and end dates to determine status at each reporting date.
- Resolve identity and assignment: Count each approved employee once while preserving legitimate concurrent positions.
- Reconcile: Bridge opening headcount through hires, exits, and corrections to closing headcount and compare with payroll.
What may be distorting the answer
- Headcount changes after job-history rows are appended.
- An employee has more than one active assignment.
- HR and payroll totals disagree at month end.
Choose the approach that matches the evidence and repetition
| Approach | When it works | What to verify |
|---|---|---|
| Snapshot row count | Clean one-row-per-person extracts | Late updates and multiple assignments |
| Effective-date model | Historical monthly headcount | Overlaps, gaps, and rehires |
| Payroll comparison | Independent control | Timing and population differences |
The decisions that matter
- Choose people, positions, or full-time equivalents and document leave, contractors, and contingent workers.
- Use effective start and end dates to determine status at each reporting date.
- Count each approved employee once while preserving legitimate concurrent positions.
- Bridge opening headcount through hires, exits, and corrections to closing headcount and compare with payroll.
Doing these steps in Prepyr
Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Build a reliable month-end HR headcount report in Excel. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Sensitive Column protection · Data profiling · Verified analysis · Report generation
A month-end HR report whose employee list, movements, department totals, and FTE can all be traced to effective-dated records and approved definitions.
Questions readers usually ask next
Should employees on leave count in headcount?
It depends on the approved business definition. Many organizations count employed people on leave but report availability separately.
What is the difference between headcount and FTE?
Headcount counts people; FTE weights capacity, such as 0.5 for a half-time role. Neither should be substituted for the other.