Sooner or later someone needs to sort, filter, pivot or export a merged report. Plain unmerging (Format → Merge cells → Unmerge) leaves the value in the first cell and blanks in the rest, so every filter and pivot is wrong.
Options
The Unmerge tab has one option and one button:
- ADVANCED OPTIONS → Copy merged value to every cell (toggle).
- Unmerge.
With the toggle on, every cell that was part of a merged block gets the block's value. The listing calls this filling "every split cell with the original merged value so no data is lost".
Before and after
| Merged (sample) | Unmerge, toggle off | Unmerge, toggle on |
|---|---|---|
| Core (3 rows) | Core | Core |
| (blank) | Core | |
| (blank) | Core | |
| Web (2 rows) | Web | Web |
| (blank) | Web |
The examples workbook's UnmergeFill tab is a ready-made test.
Step by step: make a downloaded report sortable
- Duplicate the tab (keep the readable version).
- On the copy, select the whole data range, including merged columns.
- Open Unmerge, switch Copy merged value to every cell on.
- Click Unmerge.
- Turn on a filter (Data → Create a filter) and sort by any column. It works, because every row now has its own values.
Merged headers
Reports often have a two-row header with a merged group title ("Q1" over Jan, Feb, Mar). After unmerging with fill, "Q1" appears over each month. For a single header row, combine the rows: for example with Concat horizontally on a copy, or by renaming headers to Q1 Jan, Q1 Feb. Then delete the extra header row before exporting with Table Converter.
Without the add-on
The classic workaround is a plain unmerge, then a helper column with =IF(A3="", B2, A3) filled down (B2 being the helper cell above), then copy and Paste special → Values only over the original column, then delete the helper. The add-on's fill toggle replaces that whole routine.
Practice: a report you can sort
Sample export where column A was merged for display:
| Region (merged) | Rep | Revenue |
|---|---|---|
| North (spans rows 2-4) | Asha | 120 |
| Ravi | 95 | |
| Meera | 140 | |
| South (spans rows 5-6) | John | 80 |
| Li | 110 |
Try to sort by Revenue first, without unmerging: Sheets refuses because the range contains merged cells. Then:
- Select A2:A6.
- Open Unmerge, turn on Copy merged value to every cell, click Unmerge.
- Expected: A2:A4 each read "North", A5:A6 each read "South".
- Now sort A1:C6 by column C, descending. Every row still carries its region, so the sorted list is meaningful: Meera (North) 140, Asha (North) 120, Li (South) 110, Ravi (North) 95, John (South) 80.
Run the same steps with the toggle off and sort again: rows 3, 4 and 6 lose their region and the sorted table no longer says which rep belongs where. That one toggle is the difference between a sortable table and a broken one.
Check your work
After any unmerge-and-fill, confirm there are no blanks left in the filled column. Select the column and look at the count in the bottom-right summary of the Sheets window, or use a quick formula such as =COUNTBLANK(A2:A6); the answer should be 0. If it is not, the export probably had a genuinely empty group, which you should fill by hand rather than guess.
Common mistakes
- Unmerging only part of a merged block. Select whole merged cells, or the whole column, so every block is split.
- Filling header rows that were merged across columns (a title spanning A1:C1). Copying the title into every cell repeats it three times; for headers, unmerge without the toggle and type proper column names.
Quick check
-
After a plain unmerge, why does a pivot table show "(blank)" rows?Only the first cell of each former block has the value; the rest are empty.
-
Which toggle fixes that?Copy merged value to every cell.
-
Why duplicate the tab first?To keep the readable merged version while you work on a sortable copy.






