Clean data and the formulas you need
Step 1 / 4·The flat table: one row, one record
0/4 steps done
Learning objectives — after this module you will:
- Structure data as a flat table usable for any report
- Spot and fix five common data problems
- Use the right formulas for basic HR metrics
The flat table: one row, one record
Fix the cause of 80% of reporting pain.
Most of the time spent on HR reporting goes into wrangling data, and most of that difficulty comes from how the source table is organised. The key principle: a DATA table and a REPORT table are two different things.
Two ways to organise an HR table
A table formatted for printing
“Each department is its own block with a heading in the middle of the sheet, subtotal rows interleaved, merged cells, months spread across columns (Jan, Feb, Mar…), and a blank row between blocks.”
It prints nicely but can barely be filtered, can't feed a PivotTable, and every new month means rewriting formulas.
A flat data table
“One row per record (one employee or one event), each column a field: employee ID, name, department, job title, start date, leaving date, leaving reason, line manager, contract type. No merged cells, no subtotals, no blank rows.”
From this you can build any report with a PivotTable in minutes, adding data is just adding rows, and no formula ever breaks.
Five common data problems and their fixes
- DATES STORED AS TEXT: check by alignment — real dates right-align by default. This is the number one cause of wrong tenure calculations.
- INCONSISTENT DEPARTMENT NAMES: 'HR Department', 'Dept. HR', 'HR', 'Human Resources' — use a standard list and a dropdown rather than free typing.
- MERGED CELLS: remove every merged cell from the data table. They break filtering and PivotTables entirely.
- MULTIPLE FACTS IN ONE CELL: 'Nguyen Van A - EMP001 - Production' must be split into three columns.
- STRAY SPACES AND MIXED CASE: use TRIM to strip spaces — this is the usual reason a lookup fails when the two values look identical.
Key takeaway: A flat table: one row per record, one field per column, no merged cells, no subtotals, no blank rows.