ysskrishna logoysskrishna

Chapter 5 of 6 · 3 min

Unmerge cells and fill every cell with its value

Get a merged report back into a sortable, filterable table in one step, without blank cells.

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 offUnmerge, toggle on
Core (3 rows)CoreCore
(blank)Core
(blank)Core
Web (2 rows)WebWeb
(blank)Web

The examples workbook's UnmergeFill tab is a ready-made test.

Step by step: make a downloaded report sortable

  1. Duplicate the tab (keep the readable version).
  2. On the copy, select the whole data range, including merged columns.
  3. Open Unmerge, switch Copy merged value to every cell on.
  4. Click Unmerge.
  5. 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)RepRevenue
North (spans rows 2-4)Asha120
Ravi95
Meera140
South (spans rows 5-6)John80
Li110

Try to sort by Revenue first, without unmerging: Sheets refuses because the range contains merged cells. Then:

  1. Select A2:A6.
  2. Open Unmerge, turn on Copy merged value to every cell, click Unmerge.
  3. Expected: A2:A4 each read "North", A5:A6 each read "South".
  4. 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

  1. 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.

  2. Which toggle fixes that?Copy merged value to every cell.

  3. Why duplicate the tab first?To keep the readable merged version while you work on a sortable copy.

Continue exploring

ProductGoogle WorkspaceGoogle Sheets Merge Tools

Google Sheets Merge Tools

Repeatable merges in Google Sheets, without the misclick marathon.
Google Sheets Merge Tools
Google Workspace Add-ons
Google Sheets
Google Apps Script
ProductGoogle WorkspaceGoogle Sheets Tab Manager

Google Sheets Tab Manager

Your workbook outgrew the tab bar. Clean it up in batches.
Google Sheets Tab Manager
Google Workspace Add-ons
Google Sheets
Google Apps Script
CourseCover image for Table Converter for Google Sheets: Import and Export Markdown, JSON, HTML and CSV

Table Converter for Google Sheets: Import and Export Markdown, JSON, HTML and CSV

Import Markdown, JSON, HTML and CSV tables into Google Sheets without broken columns, and export any range as clean Markdown, JSON, HTML or CSV.

Google Sheets Table Converter
Google Sheets
+1

6 chapters · 16 min

BlogCover image for How to Delete Multiple Tabs in Google Sheets at Once (Bulk Hide, Rename, Sort)

How to Delete Multiple Tabs in Google Sheets at Once (Bulk Hide, Rename, Sort)

Google Sheets Tab Manager
Google Workspace Add-ons
+1
2 min read
ProductGoogle WorkspaceGoogle Sheets Text Power Tools

Google Sheets Text Power Tools

Fix messy sheet text in place. Case, trim, strip, and extract without leaving the grid.
Google Sheets Text Power Tools
Text & Data Conversion
Google Workspace Add-ons
Google Sheets
CourseCover image for Sheets Tab Manager: Delete, Hide, Rename, Colour and Sort Tabs in Bulk

Sheets Tab Manager: Delete, Hide, Rename, Colour and Sort Tabs in Bulk

Delete multiple tabs in Google Sheets at once, hide and unhide in bulk, rename with find and replace, colour and sort tabs A–Z with the free Sheets Tab Manager.

Google Sheets Tab Manager
Google Sheets
+1

6 chapters · 24 min