The exact error message
When you try to sort, filter, or pivot data that contains merged cells of unequal size, Excel pops up this dialog:
Microsoft Excel
To do this, all the merged cells need to be the same size.
And the action you tried — sort, filter, pivot — just doesn't happen.
Why this happens (and why you can't see the merges)
Many Excel files don't come from people — they come from exports. Accounting software like QuickBooks, Xero, SAP, Oracle, Tally; ERP report builders; PDF-to-Excel converters; CRM dumps. These tools generate Excel files programmatically and use merged cells aggressively, but not for the reason humans do.
Humans merge cells deliberately — to create a banner header that spans Jan/Feb/Mar, for example. Exporters merge cells for visual styling— a row title that visually extends across several columns to look like a section divider, a grand-total row whose label is centered across the report width, spacer rows formatted with merged borders. Open the file in Excel, click any cell, and look at the Name Box: you'll often see a merged range you didn't even realise existed.
These “invisible” merges are the reason Excel refuses to sort. Sorting requires every row to occupy exactly one cell in each column. If row 5 of column A is a single cell but row 6 is part of a 7-column merge, Excel can't safely rearrange them — moving the merged row would either tear the merge apart or push it on top of single-cell rows below.
The three places this bites
- Sort.Click any column header arrow → A→Z. The error fires. Your “clean” report can't be ordered.
- Filter. Turn on AutoFilter, try to filter by a value, and Excel either refuses outright or returns the wrong rows because the merge groups break the row boundary the filter is supposed to follow.
- Pivot Table. Insert → PivotTable → select your range. Excel either rejects the range or — worse — accepts it and silently produces wrong totals because merged labels are treated as blanks for rows below the origin.
Pro Tip
Always clean your data before building a Pivot Table to avoid silent calculation errors. Excel will sometimes let you build a Pivot from merged data and produce totals that look right but quietly miss rows under merged labels. The error popup is annoying; the silent miscount is dangerous.
The fix: unmerge everything, then drop the empty noise
The root cause is structural: merged cells of unequal size. The fix has to remove all merges before sort / filter / pivot can work. ExcelCleansing's Spreadsheet Cleaner does this in two of its four passes:
Pass 01 — Unmerge
Walks every merged range in every sheet and breaks the merge. The original value stays in its top-left cell exactly where it was; every other cell in the merge becomes a regular, independent, single-cell cell. After this pass, the structural condition Excel was blocking on (merged cells need to be the same size) is no longer true anywhere — every cell is the same size, which is one cell.
Cell styling, number formats, formulas, dates, fonts, colours, and borders are preserved through the round-trip. The file looks visually the same, minus the merges.
Pass 02 — Compact
Once the merges are gone, exporters tend to leave behind a lot of empty noise: blank rows that used to be merged banners, blank spacer columns that used to be merge widths, “ghost” cells styled but holding no value. Compact removes all of it. Empty rows disappear. Empty columns disappear. The surviving data packs tight to cell A1 with no gaps.
This second pass is what often clears up the second wave of pivot-table issues — the ones where the error stops firing but the pivot still misbehaves because Excel is seeing your data as a 50-column-wide range when there are really only 7 columns of real values.
Passes 03 and 04 — for the issues you haven't noticed yet
The cleaner runs two more passes after that: Repair Headers (shifts misplaced column labels back to the column where their data actually lives — common in accounting exports), and Strip Ghosts(removes invisible non-breaking spaces and zero-width characters that quietly break VLOOKUP and Pivot Table grouping). Both run by default. You don't configure anything.
What if you want to do it manually?
You can — it just takes a lot longer. Excel-only path:
- Select all data: press Ctrl + A twice (or click the corner cell above row 1 / left of column A).
- Home → Merge & Center dropdown → Unmerge Cells. Every merge in the selection is broken at once.
- Delete the blank rows and columnsthat the unmerge revealed. Sort each column by “Blanks”, delete the blank block, repeat for each column. Tedious on a multi-sheet workbook.
- If you also want the merged values to fill down (so Pivot Tables group correctly): follow the 10-step Go-To-Special workflow we documented in the unmerge guide.
On a single sheet with one or two merges, this is fine. On a real-world accounting export with 8,000+ programmatic merges across 12 sheets, it's a half-day of work. The cleaner handles it in a few seconds.