AppGarden Structure Flattener logo AppGarden

Structure Flattener

Remove subtotal rows from an Excel™ report without losing the grouping

By Paul Kemp · Published

Subtotal rows are helpful to read and harmful to analyse. Sum a column that holds both the lines and the subtotals and everything is counted twice. Delete them blindly and you lose the grouping they carried.

The safe order

  1. Record the grand total first. You need it to prove nothing was lost.
  2. Move the grouping into a column. The section name (for example a region) usually appears in a heading row or in the subtotal's label. Put it on every line beneath it.
  3. Then remove the subtotal rows.
  4. Compare the sum of the remaining lines with the recorded grand total.

By hand

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

  1. Add a "Section" column next to the data.
  2. In the first data row of each section, type the section name. Select the column, use Home → Find & Select → Go To Special → Blanks, type = and press the Up arrow, then press Ctrl+Enter. Each blank takes the value above it.
  3. Copy the column and use Paste Special → Values so the names no longer depend on formulas.
  4. Filter the label column for "Total" or "Subtotal" and delete those rows. If the subtotals were made with Data → Subtotal, Remove All on that dialog removes them too.
  5. Remove the section heading rows, which are now duplicated in the column.
  6. Add up the amounts and compare with the grand total from step one.

Beware of a subtotal that has no label. Check that its numbers equal the sum of the rows above it before you delete it.

Reading subtotals by arithmetic

Structure Flattener removes subtotals into a Section column and reconciles the grand total against the sum of the flattened rows, column by column. If they disagree the pane says so at the top. When a row has no formula, no bold and no "total" label, it is identified only if every numeric column matches the rows above. Nesting is supported to two levels. Detection and preview are free.

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.