How to Unmerge Cells and Fill Blank Values in Microsoft Excel

Merged cells are widely regarded as one of the most frustrating formatting choices in spreadsheet design. While merging cells visually groups repeated departmental headers or dates across consecutive rows, it severely breaks essential analytical features in Microsoft Excel: column sorting fails, filter views drop blank records, and PivotTables cannot accurately aggregate fragmented categories.

The primary issue occurs when you attempt to unmerge these cells: Excel preserves the original text strictly in the top-left cell of the merged block, leaving all newly unmerged adjacent rows completely empty. Fortunately, you can unmerge cells and instantly populate all empty rows with their matching parent values across thousands of rows in just a few seconds using Excel’s built-in Go To Special tool.

Why Unmerging and Filling Down Is Critical for Data Analysis

When an Excel table contains vertically merged cells (for example, merging cells A2:A6 to label five products under “Electronics”), only cell A2 contains the text string “Electronics”. Cells A3 through A6 are technically empty underneath. If you filter for “Electronics”, Excel returns only the single top row, hiding the remaining four items.

Unmerging the range and duplicating the parent label across every corresponding row converts the visual layout into a clean, normalised database table suitable for VLOOKUP, XLOOKUP, sorting, and PivotTable analyses.

Step 1: Unmerge the Entire Column or Data Range

The first stage is releasing the merged cell boundaries across your spreadsheet:

1. Highlight the column or specific cell range containing the merged cells.
2. On the Home tab of the Excel ribbon, locate the Alignment group.
3. Click the Merge & Center dropdown arrow.
4. Select Unmerge Cells.

All previously merged blocks will separate into individual grid cells. As expected, only the top row of each previous group displays a value, with empty blank cells underneath.

Step 2: Select All Newly Created Blank Cells with Go To Special

Rather than manually copying and pasting values into each blank cell, instruct Excel to isolate every empty cell simultaneously:

1. Keep the unmerged column highlighted.
2. On the Home tab, click Find & Select on the far right of the ribbon (or press Ctrl + G, or press F5).
3. Click Go To Special… at the bottom of the dialogue box.
4. In the Go To Special window, click the radio button labelled Blanks.
5. Click OK.

Excel will instantly select only the empty cells within your highlighted range, leaving the populated header cells untouched.

Step 3: Enter the Fill-Down Formula Using Ctrl + Enter

With all blank cells highlighted and the active cursor resting inside the first empty cell (for example, cell A3):

1. Do not click anywhere with your mouse (clicking will deselect the blanks).
2. Type an equals sign followed by the cell immediately above the active cell:

   =A2

   (Tip: Simply type = and press the Up Arrow key once on your keyboard).
3. Now, press Ctrl + Enter simultaneously (do NOT press standard Enter alone).

Pressing Ctrl + Enter forces Excel to populate that relative formula into every single selected blank cell simultaneously. Instantly, every empty cell references the populated label above it, filling your entire dataset with correct departmental categories.

Step 4: Lock the Filled Data as Static Values

Because the filled cells currently contain dynamic relative formulas (=A2, =A3, etc.), sorting the column right now would scramble references. You must lock them as static text:

1. Highlight the entire column once more.
2. Copy the column by pressing Ctrl + C.
3. Right-click the selection, hover over Paste special, and click Values only (or press Ctrl + Alt + V, select Values, and press Enter).

All formulas are permanently converted into solid text strings, leaving you with a pristine, fully normalised dataset ready for enterprise reporting.