Turn a messy HRIS export into a clean headcount table

Leadership wants the monthly headcount by department on Monday. Your HRIS gives you a 30-column export with employment statuses spelled four different ways, department names that drift ("Eng," "Engineering," "ENG"), and terminated employees still in the file. What they want is a tidy table: department, headcount, maybe a split by full-time and contractor. What you have is raw data.

Bridging that gap by hand every month is a spreadsheet ritual. Encode it as a workflow instead and the report builds itself from each new export.

From raw export to report

• Statuses like "Active," "active," and "A" that should count as one.
• Department names that vary by spelling and casing.
• Terminated and on-leave employees that inflate the count if not filtered.
• Extra columns leadership never needs to see.

The headcount workflow

1. Trim & Clean the status and department columns.
2. Map Values to collapse status and department variants into canonical labels.
3. Filter Rows to active employees for the reporting date.
4. Select the handful of columns leadership actually reads.
5. Aggregate by department to produce the headcount, split by employment type.

A report you can defend

The receipt shows how the raw rows became the summary — how many were filtered out as inactive, how many counted. When someone in the meeting asks "why is Engineering down two," you can point at the workflow instead of guessing.