How to Unmerge Cells and Fill Down in Excel

The pivot-table-refuses-to-build problem

You inherited a workbook from someone who liked merged cells. Or you converted a PDF to Excel and the converter preserved the merges. Either way, you try to pivot the data and Excel gives you the unhelpful "The PivotTable field name is not valid" error. The cause is almost always merged cells in the header or in a category column.

Here's how to fix it. Three methods, with notes on when each is the right pick.

TL;DR — Key takeaways

• Merged cells break sort, filter, pivot, and lookup. Flat data is always better.
• The manual method (Home > Merge & Center to toggle off, F5 > Special > Blanks > =A2 > Ctrl+Enter) works but is fiddly.
• A free VBA macro does it in one click and reports the merge count.
• Always work on a copy of the file. Unmerging changes the structure and is hard to undo cleanly across multiple regions.

Why merged cells cause so many problems

Merged cells are visually nice. They make a category header look like it groups the rows below it. Excel hates them under the hood:

• Sort doesn't know how to keep a merged region together when sorting by another column.
• Filter can't filter on a merged column because most of the cells are technically empty.
• Pivot tables fail to build because the column has missing values for most rows.
• VLOOKUP returns blanks for the rows where the merge "shows" the value but the cell is technically empty.

The fix is the same in every case: unmerge, then fill the value down so every row has its own copy.

Method 1: The manual unmerge-and-fill

Excel's built-in workflow:

1. Select the column with merged cells.
2. Home > Merge & Center to toggle the merges off. Now most cells in the column are blank.
3. With the column still selected, press F5 to open Go To.
4. Click Special > Blanks, click OK. All the blank cells are now selected.
5. Type = then point at the cell directly above (e.g., =A2).
6. Press Ctrl + Enter. Every selected blank cell gets the value from the cell above.
7. (Optional) Copy the column, Paste Special > Values to convert the formulas to static values.

This works. It's also fiddly, especially when the merged cells are in multiple non-adjacent columns. Each column needs its own pass.

Method 2: F5 with multi-column data (more error-prone)

If you have merged cells in three different columns, you can technically do all three at once with the F5 trick by selecting all three columns first. But there's a gotcha: the =A2 reference only works for one column. For multi-column blanks-fill, you'd write =R[-1]C in R1C1 mode or you skip the multi-column shortcut and process each column separately.

Most people give up on the F5 trick after one or two columns and start clicking individual cells.

Method 3: The free VBA macro (one click, multi-region safe)

Download Unmerge Cells and Fill Down. Free .xlsm with one macro.

Steps:

1. Select the column or range with merged cells (selecting wider is fine, the macro only touches actual merges).
2. Press Alt + F8, pick UnmergeAndFill, click Run.
3. The macro unmerges every merged region in your selection and writes the value into every cell the merge used to span. The popup confirms how many merge regions were processed.

The macro processes each merged area exactly once (when it hits the top-left cell of the merge). It works across multiple columns simultaneously and writes values, not formulas, so the result survives copy-paste.

A common scenario: PDF-converted financial report

You converted a 14-region loan portfolio PDF to Excel. Column A has the region name as a merged cell spanning each region's rows. You need to pivot by region.

Manual approach:

1. Select column A.
2. Toggle Merge & Center off (200-row gaps appear).
3. F5 > Special > Blanks > =A2 > Ctrl+Enter.
4. Copy, Paste Special > Values.
5. Run pivot.

About 12 minutes if everything works. About 20 if you accidentally select the wrong starting cell on Ctrl+Enter and propagate the wrong region label down 200 rows.

Macro approach:

1. Select column A.
2. Run the macro.
3. Run pivot.

About 5 seconds total.

What about merged cells in the header row?

Headers are often the first place merges break things. If row 1 has 2024 Actuals merged across three columns and 2025 Plan merged across three more, every pivot or filter attempt sees those as text strings in only the leftmost cell of each merge.

The macro handles header merges the same way as data merges: unmerges, fills the value into every formerly-merged cell. So 2024 Actuals ends up in three header cells. You probably want different headers (2024 Actuals Q1, 2024 Actuals Q2, 2024 Actuals Q3). Edit those manually after running the macro.

What about "Center Across Selection" formatting?

Some users use "Center Across Selection" (a horizontal alignment setting) instead of true merging. It looks the same visually but doesn't actually merge cells. The macro skips these because they aren't real merges. If you have a sheet with Center-Across-Selection, you don't need to unmerge — sort and filter already work because each cell is independent.

Frequently asked questions

Will it touch merged cells outside my selection?

No. The macro only processes merged regions whose top-left cell falls inside your selection. Selection-aware behavior keeps the macro safe to run on a column-only selection without affecting unrelated merges elsewhere on the sheet.

What about empty merged cells?

Empty merged cells get unmerged and stay empty. The macro copies whatever value was in the top-left of the merge, even if that's blank. To fill the resulting blanks afterward, run Fill Blank Cells With Value Above on the same column.

Does it preserve formatting?

Yes. The macro only changes which cells are merged and what value they contain. Font, color, borders, and number format on the unmerged cells stay as they were. After unmerging, formatting that previously inherited from the merged region may look different because each cell now holds its own copy.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running undoes the unmerge and the fill in one step. Always work on a copy of the file if it's the only one. As always with structural changes, save before running.

What to do next

After unmerging, if your column still has blank cells (e.g., the merge regions were sparse), run Fill Blank Cells With Value Above. If the source data also has blank rows scattered through it, Remove Blank Rows cleans those out next. After that, your data should pivot and filter cleanly.