AppGarden Structure Flattener logo AppGarden

Structure Flattener

Merged titles and two-row headers in Excel™: turn them into one clean header

By Paul Kemp · Published

A report header might say "Q1" across three columns, with "Units", "Revenue" and "Margin" beneath. To a person, that reads as "Q1 Units", "Q1 Revenue" and "Q1 Margin". To a table or a pivot, the columns have no usable names.

Why merged cells cause trouble

Only the top-left cell of a merged block holds the value. Every other cell in the block is empty. Sorting, filtering and pivots then behave unpredictably, and a table cannot be created over a range with merges in it.

Combine by hand

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

  1. Select the merged header cells and choose Home → Merge & Center to unmerge. The value stays in the first cell.
  2. Fill the group name across the others. Select the row, use Home → Find & Select → Go To Special → Blanks, type =, press the Left arrow and press Ctrl+Enter.
  3. In a new row, join the two header rows: =A1&" "&A2. Fill across.
  4. Copy that row and Paste Special → Values into the row that will be the header.
  5. Delete the old header rows.
  6. Give every column a unique name. Duplicate names get renamed when you create a Table.
  7. Press Ctrl+T to create the Table.

Read the new names aloud. If "Q1 Units" and "Q2 Units" are both there, you have kept the information.

Check the names before anything is written

Structure Flattener joins names from multi-row headers into one column name and shows them under "Check the names" so you can edit any before it writes. Those joined names and any section names it inferred from the report are the two judgement calls you can veto first. The flat table is written as an Excel™ Table on a new sheet, and your source sheet is not touched. 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.