AppGarden Structure Flattener logo AppGarden

Structure Flattener

Why you can't pivot a formatted Excel™ report, and how to flatten it

By Paul Kemp · Published

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

Flatten it by hand

These steps apply to Excel™ in Microsoft 365. Work on a copy of the sheet.

  1. Delete the title rows so the header is the first row, and unmerge any merged cells.
  2. 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.
  3. 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.
  4. 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.
  5. Delete the subtotal and grand total rows once you have recorded the grand total to check against.
  6. Select the block and press Ctrl+T to make it a Table. Then Insert → PivotTable.
  7. 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.