Headcount and attrition sound like the same report. They aren’t. Headcount is a snapshot — how many people are on the payroll at a given moment. Attrition is a rate — how many people left relative to how many were there to begin with. Build one pivot table trying to answer both at once, and you’ll end up with a number that looks plausible but measures neither question cleanly.
That confusion is the single biggest reason HR pivot tables get rebuilt from scratch every quarter instead of just updated. Below are the five pivot table setups that consistently cover the questions HR teams actually ask, ranked from the one you’ll build constantly to the one you’ll only need a few times a year. Each entry includes the fields to use, the setting most people get wrong, and why it sits where it does on the list.
1. The Headcount Snapshot by Department (Most Essential)
This is the pivot table you’ll rebuild or refresh more than any other, because it answers the question that comes up in nearly every meeting: how many people do we have, and where are they sitting right now.
Drag Department into Rows, and drop Employee ID (or any unique identifier) into Values, set to Count, not Sum. That distinction matters — summing an ID field produces a meaningless total, while counting it gives you the actual headcount per group. Add a second row field like Job Level or Employment Type if you need a breakdown finer than department alone.
The mistake that shows up constantly here is filtering the source data incorrectly before the pivot table ever sees it — including terminated employees in a “current headcount” view because the termination date column wasn’t filtered out. Before building this pivot, add a filter or a helper column in the source data that flags active employees only, then base the pivot table on that filtered range. Otherwise your headcount number quietly includes people who left six months ago.
This setup ranks first because almost every other analysis on this list depends on getting this baseline right first.
2. Headcount Trend Over Time (Second Most Common, Easy to Get Backwards)
Once you can answer “how many people right now,” the next question is almost always “how has that changed.” This is a different pivot table, not a modification of the first one, and treating them as interchangeable is where a lot of HR reporting goes wrong.
Put a date field — hire date works if you’re tracking growth, or a monthly snapshot date if your source data captures headcount at regular intervals — into Rows, and group it by Month and Year. Add Department into Columns if you want the trend broken out by group, and keep Employee ID in Values as a Count.
The setting that trips people up is the difference between counting hires in a period versus counting total headcount as of that period. Counting hire dates by month tells you how many people joined in March. It does not tell you how many people were employed as of March, because it says nothing about who left before then. If you need the second version — a true point-in-time headcount trend — your source data needs a snapshot structure, with one row per employee per month, not just a single hire date and a single termination date. That’s a data structure problem, not a pivot table setting, so it’s worth confirming before you build this one.
3. Attrition Rate by Department (The One Most Often Calculated Wrong)
This is where headcount and attrition finally meet, and it’s also where the most common calculation error in HR reporting shows up: treating attrition rate as terminations divided by current headcount, instead of terminations divided by average headcount over the period.
Start with Department in Rows. Add a Values field counting terminations — this usually means filtering or flagging rows where a termination date falls within your reporting period, then counting those. Add a second Values field for average headcount over the same period, which typically needs to be calculated outside the pivot table first, since a pivot table doesn’t average a changing population over time on its own.
Once both numbers sit side by side in the pivot table, add a calculated field (PivotTable Analyze, then Fields, Items & Sets, then Calculated Field) dividing terminations by average headcount. Label it clearly — “Attrition Rate” rather than something ambiguous — because this is the number that ends up in leadership decks, and a mislabeled column here causes more downstream confusion than almost any other mistake on this list.
This ranks third rather than first because it depends entirely on getting Setups 1 and 2 right beforehand. An attrition rate calculated against a flawed headcount baseline will look precise while being wrong.
4. Attrition by Tenure Band (Reveals What a Flat Rate Hides)
A single attrition percentage for the whole company can hide a serious problem: if attrition concentrates heavily in the first six months of employment, that’s a different issue — and a different fix — than attrition spread evenly across ten years of tenure. A flat company-wide rate averages those two situations into a number that points leadership in the wrong direction.
To build this, you need a tenure calculation in the source data first — typically termination date minus hire date, expressed in months. Group that tenure field into bands using the pivot table’s Group feature: 0–6 months, 6–12 months, 1–3 years, 3–5 years, 5+ years. Put those bands in Rows, and count terminations in Values.
What this pivot table exposes, almost every time it’s built for the first time, is a spike in the earliest tenure band. That pattern usually points toward onboarding or hiring-fit issues rather than compensation or management problems, which are the more common (and more expensive) assumptions leadership tends to reach for first. It’s worth building this one before recommending any retention initiative, since the fix for early-tenure attrition and the fix for long-tenure attrition rarely overlap.
5. Attrition by Hire Cohort (Situational, But Reveals Long-Term Patterns)
This is the setup you’ll reach for least often, but it answers a question the other four can’t: is a specific hiring class — say, everyone brought on during a particular quarter or under a particular manager — leaving at a different rate than employees hired at other times.
Group employees by hire quarter or hire year in Rows, and add tenure band or exit reason as a second row field nested underneath. Count terminations in Values, and compare the resulting rate across cohorts. If one hiring wave shows a termination rate noticeably higher than the surrounding cohorts, that’s a signal worth chasing down — it might point to a hiring manager, a specific job requisition, a compensation band set incorrectly at the time, or an onboarding process that changed and later reverted.
This ranks last not because it’s less valuable, but because it requires a longer data history to produce a reliable read, and most teams only pull it out when something in Setups 3 or 4 has already flagged a pattern worth investigating further.
Choosing Which One to Build First
If you’re starting from scratch with HR data, don’t try to build all five in one sitting. Setup 1 has to exist before Setup 2 makes sense, and Setups 3 through 5 are only as reliable as the headcount baseline underneath them. Get the snapshot right, confirm the trend reflects true point-in-time headcount rather than raw hire counts, and only then move into rate calculations and cohort breakdowns.
The recurring failure across all five isn’t a pivot table setting — it’s source data that doesn’t distinguish clearly enough between “employed as of this date” and “hired on this date” or “terminated on this date.” Fix that distinction once in your source table, and every pivot table on this list becomes noticeably easier to trust.
Which of these five are you missing right now — the baseline snapshot, or the rate calculation sitting on top of it?