Unmerge Cells and Fill Down

What it does

Merged cells make a spreadsheet look tidy and behave terribly. They break sort, filter, pivot, lookup, and basically every analytical thing Excel does well. The macro walks your selection, finds every merged cell, captures the value (which lives only in the top-left of the merge), unmerges, and writes that value into every cell the merge used to span. After running it, your data is a normal flat table that pivots and filters cleanly.

Real-world example

Picture a treasury analyst at a regional credit union, the day after the monthly board pack is due. Her board chair sends her a PDF of last month's loan portfolio summary that he wants reformatted into Excel for next month's discussion. She runs the PDF through a converter, gets a workbook that mostly looks right, and starts trying to pivot the data by branch. Pivot Table refuses with the cryptic error "The PivotTable field name is not valid."

The reason: the original report used merged cells for branch labels. Branch "Northgate" appeared once at the top of a 23-row block, and the rows underneath were blank in column A but visually grouped because the cell was merged across all 23 of them. The PDF converter preserved the merge, so column A is now a Swiss-cheese mess of one labeled merged region per branch.

Her current workflow: select the merged range, hit Home > Merge & Center to toggle the merge off, then hit F5 > Special > Blanks to select all the now-empty cells, type =A2, hit Ctrl+Enter. Repeat for each merged region. The 14 branches take her about 15 minutes of fiddly clicking, and twice she Ctrl+Enters into the wrong direction and has to undo.

With this macro she selects the column, runs it once, and the macro unmerges all 14 regions and copies each branch name into the 200-or-so rows that branch used to span. The popup says "Unmerged 14 merged area(s)." She runs her pivot. It works. Total elapsed: about 5 seconds.

On a 14-region merged report, expect about 14 minutes saved. The bigger win is making the file pivot-ready in one click instead of trial-and-error fill-down operations that occasionally clobber the wrong cells.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the column or range that contains the merged cells. (Selecting a wider range is fine; the macro only touches actually-merged cells.)
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick UnmergeAndFill, click Run.
3. The macro unmerges every merged region in your selection and copies the value into every cell the merge used to cover. The popup tells you how many merged regions were processed.

FAQ

What happens if a merged cell is empty?

Empty merged cells get unmerged and stay empty. The macro copies whatever value was in the top-left cell of the merge, even if that's blank. If you want to fill blanks afterward with the value from the row above (a common follow-up), run Fill Blank Cells With Value Above on the same column.

Will it touch merged cells outside my selection?

No. The macro processes only merged regions whose top-left cell falls inside your selection. If a merged region partially overlaps your selection (which is rare and usually a sign the selection was wrong), the merge is still processed because its top-left cell is what triggers the work.

What about merged cells with center-across-selection formatting instead of true merge?

Center-across-selection is not a real merge; it's a display-only setting. The macro skips those cells because MergeCells returns False for them. If you need to clean up center-across-selection, select the cells, press Ctrl+1, go to the Alignment tab, and change Horizontal to "General".

Does it preserve cell formatting like bold or color?

Yes. The macro only changes which cells are merged and which cells contain the value. Font, color, borders, and number format on the unmerged cells stay as they were. Note that after unmerging, cells that previously inherited formatting from the merged region may look different (because they each now hold their own copy of the value).

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 your file if it's the only one you have. The macro runs entirely offline. Your data never leaves your machine.