How to Clean Data for a Pivot Table in Excel
To clean data for a pivot table in Excel, unmerge labels, fix numbers stored as text, trim spaces, and remove blank rows and duplicates with free macros.
2026-07-28
You drop a fresh export into a PivotTable, and it goes wrong in one of the quiet ways: the totals don't match, "Sales" shows up as two separate rows, a whole region is missing, or Excel refuses to build the table at all. The pivot isn't broken. The data under it is. To clean data for a pivot table in Excel, you fix a short list of things that trip pivots up, and most of them are one-click jobs.
This is the pre-pivot prep step, aimed at analysts who'd rather spend the time on the analysis, using free macros for the fixes and Excel's built-in tools where they fit.
TL;DR: clean data for a pivot table in Excel
- Unmerge the labels. Merged cells leave blanks that a pivot can't group.
- Fix numbers stored as text. They won't sum, so your totals silently come out wrong.
- Trim stray spaces. "Sales" and "Sales " become two categories in the pivot.
- Remove blank rows. A blank row truncates the range the pivot picks up.
- Remove duplicates. Duplicate rows inflate every count and total.
With the macros loaded this is a two-minute pass. Skip it and you'll spend longer than that hunting for why the numbers don't tie out.
Why pivots break: the source data, not the pivot
A PivotTable needs a clean, flat table: one row of unique headers, one type of data per column, no gaps, no merges. Real exports rarely arrive that way, and Microsoft's top ten ways to clean your data is a good primer on the mess.
The insidious part is that most of these problems don't throw an error. A pivot built on numbers-stored-as-text doesn't turn red. It just sums them as zero and hands you a confident, wrong total. So the fixes below aren't optional polish, they're the difference between a right answer and a wrong one you don't notice.
Step 1: Unmerge and fill down the labels
Merged cells are the number-one pivot killer. A merged "North" heading spanning ten rows looks fine, but Excel only stores the value in the top cell, so the other nine are blank and the pivot can't group them.
Select the range and run Unmerge and Fill Down. It unmerges every merged area and copies the value into every cell it used to cover, so all ten rows now say "North." If your label column has plain blank cells rather than merged ones (common in grouped exports), fill those blanks down with the value above instead. Either way, every row ends up with its own label.
Step 2: Turn text-numbers into real numbers
Numbers that arrive as text are the quiet killer. They often come with a leading apostrophe or a stray space, they sit left-aligned in the cell, and a pivot sums them as zero. Your grand total looks plausible and is completely wrong.
Select the value columns and run Convert Text to Numbers. It strips the apostrophes and formatting that force a number to text and writes back a real number, so SUM and AVERAGE in the pivot actually work. Leave text columns like names and IDs out of the selection.
Step 3: Trim the stray spaces
A trailing space is invisible to you and load-bearing to a pivot. "Sales" and "Sales " are different strings, so the pivot lists them as two separate rows and splits their totals between them.
Run Trim Whitespace From All Cells across the whole range. It removes leading and trailing spaces and collapses double spaces to one, so your categories collapse back into single rows. This is the fix for the maddening "why is this category listed twice" moment.
Step 4: Remove blank rows
A single blank row in the middle of your data can cut the range a pivot auto-detects, so it silently analyzes only the rows above the gap. Select your data and run Remove Blank Rows. It deletes any row where every cell is empty, bottom to top, leaving one continuous block the pivot reads in full.
Keep an eye out for blank columns too. A fully empty column splits the range the same way a blank row does.
Step 5: Remove duplicate rows
Duplicate records inflate every number in the pivot. The same transaction twice doubles its contribution to a total; the same customer twice doubles a count. Select your data including the header row and run Remove Duplicates by Multiple Columns, matching on the columns that make a row unique, like an ID or an invoice number. It keeps the first copy and drops the rest.
New to running any of these? The 30-second setup gets you going.
Now build the pivot
With the data clean, convert it to a Table first with Ctrl + T so the pivot's range expands automatically when you add rows. Then Insert a PivotTable as usual. Because the source is now flat, typed, and gap-free, the totals tie out and every category appears once.
The hot take: a broken pivot rarely errors, it just lies
People expect a data problem to announce itself with #REF! or a red cell. Pivot-source problems don't. Merged cells, text-numbers, and trailing spaces all produce a pivot that builds fine and looks right, while quietly undercounting or double-listing.
That's why "the pivot table isn't working" is almost always a data-cleaning problem wearing a pivot-table costume. Clean the source first, every time, and you stop debugging the pivot for issues that were never in the pivot.
Common pitfalls
- Converting ID columns to numbers. Invoice numbers and zip codes with leading zeros lose them when forced to numeric. Only convert real value columns.
- Leaving merged cells in the header row. A merged header breaks the pivot's field list. Unmerge headers too, and give every column a unique name.
- Deduping on the wrong columns. Match on the columns that define a unique record, not a single column that repeats legitimately.
- Cleaning the only copy. Work on a copy of the export so you keep the raw source as a reference.
Frequently asked questions
How do I clean data for a pivot table in Excel?
Fix the five things that break pivots: unmerge and fill the label cells, convert numbers stored as text into real numbers, trim stray spaces, remove blank rows, and remove duplicate records. Each is a one-click macro. Then convert the range to a Table with Ctrl + T and build the pivot on the clean data.
Why is my pivot table showing the same category twice?
Almost always a trailing space. "Sales" and "Sales " are different text to Excel, so the pivot lists them separately and splits their totals. Run a trim-whitespace pass over the category column and the two rows collapse into one. Inconsistent capitalization can do the same if your pivot treats case as distinct.
Why are my pivot table totals wrong or zero?
Usually the numbers are stored as text. Text-numbers sum to zero in a pivot without any error, so the total looks fine but isn't. Select the value columns and run the convert-text-to-numbers macro, then refresh the pivot. Left-aligned numbers and a small green triangle in the corner are the tell.
Do merged cells break pivot tables?
Yes. Excel stores a merged value only in the top-left cell, leaving the rest blank, and a pivot can't group blank labels. Unmerge the cells and fill the value down into every row first. This is the single most common reason a PivotTable won't group a column the way you expect.
Do these macros work on Excel for Mac?
Yes. The unmerge, convert, trim, remove-blank-rows, and dedupe macros all run on Excel 2016 and later on Windows and Mac. They avoid Windows-only features, so an analyst switching between machines gets identical results.
What to do next
Run the five fixes once, and the pivot stops fighting you. Clean the source, convert to a Table, and the totals tie out on the first try.
To clean data for a pivot table in Excel on your next export, browse the full set of Excel tools for data analysts, or start with Unmerge and Fill Down, the fix that solves the most pivot headaches.