Clean data before building a pivot table
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.
Cleaning Data in Excel: Microsoft Excel Crash Course
Simon Sez IT
Open on YouTube (opens in a new tab)
Pivot tables are excellent at summarizing data. They are not excellent at repairing messy source data.
Use one header row
Every column should have a clear header. Avoid merged cells, blank headers, and multi-line title sections above the data.
Fill required fields
Blank categories create confusing pivot table groups. Filter important columns for blanks before building the pivot.
Check dates
Dates stored as text will not group correctly by month, quarter, or year. Test date fields before relying on time-based pivot reports.
Remove duplicate records
Duplicate rows can double-count totals. Confirm whether duplicates are true duplicates or valid repeated transactions.
Compare totals
After building the pivot, compare the grand total with the source data total. That one check catches many avoidable mistakes.
Go deeper with this skill
Turn messy imported data into a reliable table that formulas, pivots, and reports can use safely. For this article, the goal is to practice: Check headers, blanks, duplicates, dates, and totals before creating pivot tables from spreadsheet data.
Practice workbook setup
Copy messy sample data to a raw-data sheet and keep the original untouched while you clean a separate working copy.
Practice workflow
- Identify the expected columns and data types before changing anything.
- Remove obvious blanks and duplicates, then check whether any removals need business approval.
- Standardize spacing, casing, dates, and number formats.
- Create a small before-and-after table so the cleanup can be reviewed.
Quality checks
- The row count before and after cleanup is explainable.
- Key columns have no unexpected blanks.
- Dates, IDs, and amounts are stored as the correct data type.
Common mistakes
- Cleaning directly over the only copy of the data.
- Removing duplicates without deciding which columns define a duplicate.
- Fixing visible rows while hidden filtered rows stay messy.
Next actions
- Convert the cleaned range into an Excel Table.
- Repeat the same cleanup with Power Query if the data will arrive again.
Formula focus: even if this workflow is not formula-heavy, add one check cell that confirms the final output still matches the source data.
