How to turn merged cells, multi-row headers and crosstabs in Excel into a flat table

Report-style spreadsheets are built to be read, not used: the header is merged across two rows, a region name is merged down several rows, and the months run across the page. Sorting fails and a PivotTable cannot use it as a source. These are Excel's own ways to do the three jobs (unmerge and fill down, combine header rows, unpivot a crosstab) so you end up with one row per record.

Hullspark · updated 2026-10-22

Short answer

The target is a table with one header row, one record per row, no merged cells and no gaps. Unmerging leaves the value in only one cell＊1, so select the blank cells and make each equal to the cell above＊2. Join header rows with the & operator＊3, and turn columns that run across the page into rows with Power Query's Unpivot＊4.

Menu names differ between Excel versions. This page follows Microsoft 365 and Microsoft's articles. If you do not see a name, look for the nearest wording on the ribbon.

Step 1: Unmerge cells and fill down

Before (region merged over two rows) and after
Before: Region	Store	Sales	After: Region	Store	Sales
East (merged)	Sapporo	120	East	Sapporo	120
East (merged)	Sendai	95	East	Sendai	95
West (merged)	Osaka	210	West	Osaka	210
West (merged)	Fukuoka	130	West	Fukuoka	130

Save a copy first. Data discarded when cells were merged does not come back＊1.
Select the merged column. Home > the Merge & Center down arrow > Unmerge Cells＊1.
Keep the column selected: Home > Find & Select > Go To (or Ctrl+G) > Special, choose Blanks, OK＊2. Only the gaps are selected.
With the blanks still selected, type =, press the Up arrow once (the formula points at the cell above), then press Ctrl+Enter to fill them all.
Copy the column and use Paste Special > Values. Left as formulas, the cells point at their neighbours and the values shuffle when you sort.

Watch out: Blanks also selects cells that were empty from the start. Run it on the unmerged columns only (region, department), not on data columns where an empty cell is meaningful. For headers merged sideways, press the Left arrow in step 4 instead.

The Power Query way: Load the table with Data > From Table/Range＊7. Gaps that came from merged cells arrive as null. Right-click the column > Fill > Down, and the nulls take the value above＊5. If cells look empty but are not filled, first use Transform > Replace values with Value to find left blank and Replace with set to null＊5.

A header with "Tokyo, Tokyo, Osaka, Osaka" above "Sales, Count, Sales, Count" should become one row: "Tokyo_Sales", "Tokyo_Count" and so on.

If the top row is merged sideways, unmerge it and fill the blanks from the left cell (type =, press Left arrow, Ctrl+Enter).
In a spare row under the header, enter =A1&"_"&A2 and copy it across. Microsoft's article joins cells with & and puts a separator in quotation marks, for example =A2&" "&B2＊3. The CONCAT function does the same＊3.
Paste that row as values, then delete the two original header rows.

Watch out: a column whose top cell is empty (a "Name" column with text only in the second row) gets a leading underscore, so fix it by hand or use a formula that skips the empty part. Pick a joining character (_ or -) that you can split on later.

Step 3: Unpivot a crosstab (months or regions across the page)

A crosstab has stores down the side and months across. To feed a PivotTable or a database you want three columns (store, month, sales), one record per row. Flatten the header first (step 2).

Click a cell in the table and choose Data > From Table/Range. If the data has a header row, tick My table has headers＊7.
In the Power Query Editor, select the columns to keep as they are (store name) and choose Transform > Unpivot Other Columns＊4.
The old headers go into one column and the cell values into another, so each row is one record. The two new columns are usually named Attribute and Value＊6.
If the headers were joined like "Tokyo_Sales", use Split Column > By Delimiter on the "_"＊8.
Home > Close & Load puts the result back on a sheet＊9.

Microsoft notes that with Unpivot Other Columns, columns added later are unpivoted on refresh too. If only the columns you picked should ever be unpivoted, use Unpivot Only Selected Columns instead＊4. Delete total and subtotal rows and columns first; left in, they become records of their own.

If you repeat this every month

For files that arrive monthly in the same layout, I made a Windows app, Table Flattener by Hullspark, which is available on the Microsoft Store. Drop a file and it estimates the table's layout and shows the original and the result side by side. Unmerged cells, filled blanks and set-aside subtotal rows are colour-coded, and it asks you to confirm when it is unsure. The result is saved as a new file; your original is not changed.

What it does not do: the estimate can be wrong. The app has been tested on generated sample tables, not yet on a wide range of real Excel files, so check the preview before you save. Formulas, charts and formatting are not carried over; only values are written.

View on the Microsoft Store

FAQ

Go To Special > Blanks also filled cells that were really empty.

Blanks selects every empty cell in the selection and cannot tell a gap left by unmerging from a cell that was always empty. Run it only on the columns that were merged (region, department), not on data columns where an empty cell means something.

After unmerging, only one cell has the value.

That is how Excel works. Microsoft's article says the contents of the other cells are deleted when you merge, so they do not come back on unmerge. The value stays in one cell and the others are blank; fill them with the formula steps above.

Unpivot Columns or Unpivot Other Columns?

Unpivot Columns turns the columns you selected into rows. Unpivot Other Columns turns everything you did not select, and Microsoft notes that columns added later are unpivoted on refresh too. Select the columns to keep as they are (store, region), then choose Unpivot Other Columns. To unpivot only the columns you picked, even after a refresh, use Unpivot Only Selected Columns.

Does the app read every table correctly?

No. It estimates the layout, shows the original and the result side by side and asks when it is unsure, but the estimate can be wrong. It has been tested on generated sample tables, not yet on a wide range of real Excel files, so check the preview before you save.

Sources

＊1 Microsoft Support: Merge and unmerge cells in Excel: https://support.microsoft.com/en-us/office/merge-and-unmerge-cells-in-excel-5cbd15d5-9375-4540-907f-c673a93fcedf
＊2 Microsoft Support: Find and select cells that meet specific conditions in Excel: https://support.microsoft.com/en-us/office/find-and-select-cells-that-meet-specific-conditions-in-excel-2d686424-6150-4015-a8e4-a5990f4d7e3a
＊3 Microsoft Support: Combine text from two or more cells into one cell in Microsoft Excel: https://support.microsoft.com/en-us/office/combine-text-from-two-or-more-cells-into-one-cell-in-microsoft-excel-81ba0946-ce78-42ed-b3c3-21340eb164a6
＊4 Microsoft Support: Unpivot columns (Power Query): https://support.microsoft.com/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098
＊5 Microsoft Learn: Fill values in a column (Power Query): https://learn.microsoft.com/en-us/power-query/fill-values-column
＊6 Excel University: Unpivot with Power Query: https://www.excel-university.com/unpivot-with-power-query/
＊7 Microsoft Support: Import data from data sources (Power Query): https://support.microsoft.com/office/import-data-from-data-sources-power-query-be4330b3-5356-486c-a168-b68e9e616f5a
＊8 Microsoft Learn: Split columns by delimiter (Power Query): https://learn.microsoft.com/en-us/power-query/split-columns-delimiter
＊9 Microsoft Support: Import from an Excel table: https://support.microsoft.com/en-us/excel/get-started/import-from-an-excel-table

Checked 2026-10-22. Menu names follow Microsoft's support and Learn articles and were not checked on every version. Every menu path above is taken from a Microsoft page listed in the sources, not from a test on a real copy of Excel. Microsoft and Excel are trademarks of Microsoft Corporation. This page and the app are not affiliated with, made by or endorsed by Microsoft.

Questions: Support or support@hullspark.com. Privacy policy