Merged cells stop Excel sorting and pivoting: how to flatten a printed-style report safely

What merging actually does to your data

Merging cells keeps only the upper-left cell's contents. Microsoft's page states that the contents of the other cells are deleted, and advises copying anything you need first. Unmerging reverses the shape, not the loss: the page says the data in the merged cell moves to the left cell, so a region name that appeared to sit above three rows now sits in one cell with two empty cells below it.

This matters in two ways. A report where a label covers several rows has that label stored once. And if someone merged cells that each held different data some time ago, the discarded values are gone from the file.

What a flat table needs

Microsoft's guidance for PivotTable source data describes what any analysis wants: tabular layout, no blank rows or columns, a single header row of distinct labels, no merged cells and one kind of data in each column. It also says an Excel table as the source includes rows added later when the PivotTable is refreshed.

The practical consequence is that every record carries its own group label, so each row can be sorted or filtered alone. Spacer rows, repeated header blocks and subtotal lines are layout, not records. Left in, they get counted, double-counted or sorted into the middle of the data.

• One header row.
• One record per row, with every label repeated on every row it applies to.
• No subtotal rows inside the data; totals are recomputed beside it.
• One data type per column.

Find the merges before you touch them

Make a copy of the file. In Excel, choose Find & Select, then Find, then Format, then the Alignment tab, tick Merge cells and select Find All. Microsoft documents this route; it lists every merged cell. Count them and note which are title blocks, which are group labels and which are column headings. Excel does not sort a column that contains merged cells, so any merge inside the data area must go before sorting is possible.

Flatten without losing meaning

Unmerge, then fill each label down onto the rows it covered, but only where you can say what it covered. Then list the spacer, repeated heading and subtotal rows by type before removing them. Prove that nothing else was lost: the number of original rows must equal the number of records plus the number of removed rows, each numeric column's total over the records must match the total in the original records, and each original subtotal should equal a recomputed one.

Finish with a sample. Take twenty records at random and compare each one's group label with the label that covered it in the original. This is the check that catches a label filled onto the wrong rows, which no total will reveal.

Cases that do not fit, and the paid job

A label that could belong to the rows above or the rows below cannot be flattened safely until someone who knows decides. Layouts that change from block to block need a rule for each. Data already lost to earlier merging cannot be recovered. The numbers in the original may also be wrong; flattening does not make them right.

One sheet of up to 5,000 rows in one repeating layout can be flattened as a job from £145 (an untested proposal, with the final price confirmed after we see the layout description), accepted by the single header row, a merged-cell search that finds nothing, the row and total reconciliation and the label sample, with payment after your sign-off. It does not build a PivotTable or dashboard. The first enquiry needs a description of the layout and the row count, never the report.