Why you can't pivot a formatted Excel™ report, and how to flatten it
A report that reads well often cannot be pivoted. It has a title on top, headers over several rows, section headings, subtotal rows, spacer rows and a "prepared by" footer. A PivotTable wants the opposite: one header row, then one record per row, with nothing in between.
What stops a pivot working
- A title or banner above the header.
- Merged or multi-row headers, so a column has no single name.
- Section headings that carry information (a region name) in a row of their own, not in a column.
- Subtotal and grand total rows that would be counted twice.
- Spacer rows and a footer.
Flatten it by hand
These steps apply to Excel™ in Microsoft 365. Work on a copy of the sheet.
- Delete the title rows so the header is the first row, and unmerge any merged cells.
- If the header spans two rows, join them into one in a new row, for example
=A3&" "&A4, then paste as values and delete the originals. - Delete spacer rows. Select the column, use Home → Find & Select → Go To Special → Blanks, and delete the rows only if every blank is a spacer.
- Move section names into their own column. Add a column, fill the section name on every data row beneath it, and then delete the section heading rows.
- Delete the subtotal and grand total rows once you have recorded the grand total to check against.
- Select the block and press Ctrl+T to make it a Table. Then Insert → PivotTable.
- Add up the flat table and compare with the report's grand total. If they differ, something was lost.
Let it read the layout
Structure Flattener reads the layout and identifies titles, headers, sections, subtotals, spacer rows and footers as structure, then writes one flat Excel™ Table to a new sheet named after the source. Your original sheet is never changed, and a summary compares the report's grand total with the sum of the flattened rows. Detection and preview are free and unlimited, and the free flatten covers the first 100 rows of a report.
See Structure Flattener for Excel™
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Structure Flattener is not affiliated with or endorsed by Microsoft.