Merged cells break sorting and filtering: unmerge and fill down

What merging does to your data

Merging A2:A4 looks like one cell labelled North. Underneath, A2 holds North and A3 and A4 are empty. Every tool that reads rows, from the sort and filter commands to Power Query and lookups, sees those empty cells.

Filter Region for North and you get Ali's row only: 4,200 instead of the 10,225 North actually sold. Combine this sheet with others and three of five rows have no region.

The errors you will see

• Sorting a range with merged blocks of different sizes: "To do this, all the merged cells need to be the same size."
• Filters list the value once and show only the first row of each merged group.
• Copying or pasting into a range that partly overlaps merged cells: "We can't do that to a merged cell."
• VSTACK or other spilled formulas return #SPILL! if their output area has merged cells.
• Pivot tables and Power Query show (blank) or null for the lower rows.

Unmerge and fill down

1. Select the column (or the whole data range) and click Home > Merge & Center to unmerge. Every value now sits in the first row of its old block.
2. With the range still selected, press F5 > Special > Blanks > OK. Only the empty cells are selected.
3. Type = and press the Up arrow (so the formula is =A2 for the first blank), then press Ctrl+Enter to fill every selected blank.
4. Select the column again, copy, and Paste Special > Values so the formulas become fixed text.

Step 2 selects every blank in the range, including genuinely empty cells that should stay empty. Limit the selection to the columns that were merged.

In Power Query, the equivalent is Transform > Fill > Down on the column after loading; merged cells arrive as a value followed by nulls, and Fill Down replaces the nulls. That step repeats on every refresh, which suits monthly reports that keep arriving merged.

Keep the look without merging

For headings across columns, use Center Across Selection (Format Cells > Alignment > Horizontal). The text appears centred across the cells but each cell stays separate, so sorting, filtering and selecting columns work. Step-by-step instructions are in Center Across Selection. For grouped labels down a column, repeat the value on every row and use conditional formatting (font colour matching the background when the value equals the row above) if you want the repeats hidden.

When merging is the right call, for printed titles for example, the trade-offs are covered in how to merge cells in Excel and the merge cells and columns guide.

Before you combine files

Unmerge and fill down in each source before stacking, or include a Fill Down step in the query that combines them. If header rows are merged too, flatten them first; see combining files with merged header rows. Analistable reads sheets you load in the browser and does not change your files, so filling the blanks in the source is still the fix that sticks.

A formula alternative to unmerging

If you cannot change the source sheet, for example a report that is regenerated every week, read the merged column through a formula that carries the last non-blank value down. In Microsoft 365, SCAN does this in one cell:

=SCAN("", A2:A6, LAMBDA(prev, cur, IF(cur = "", prev, cur)))

For the example above it returns North, North, North, South, South. Put it in a helper column and sort, filter or combine on that instead of the merged column. In older versions, =IF(A2="", B1, A2) in B2, filled down, gives the same result (B1 should hold the column heading or the first value).

Beware of fill-down on genuinely missing values. If a row was blank in the original data, not because of merging, filling it with the value above invents data. Before filling, check how many blanks you expect: with merged blocks of three rows and one value each, roughly two-thirds of the column should be blank. A very different ratio means something else is going on. Count the blanks with =COUNTBLANK(A2:A500) before unmerging and again afterwards; the second number should be higher by exactly the number of cells that were hidden under merges, and zero after the fill. If it isn't zero, look at the remaining blanks before deciding whether they are real gaps in the data.

Merged cells across columns, typically a heading over several columns, are a different problem: they affect column names rather than rows. Those are covered in the separate page on merged header rows linked below.

Frequently asked questions

Why can't I sort data with merged cells?

Excel only sorts merged blocks when every one is the same size. Unmerge them and fill down the values first.

Why does my filter show only one row per group?

The value is stored only in the first row of each merged block; the rest are blank, so they don't match the filter.

How do I unmerge all cells at once?

Select the whole sheet with Ctrl+A and click Merge & Center to toggle merging off, then fill down the blanks.

Does Fill Down in Power Query change my source file?

No. Power Query works on a copy of the data in the query, so the source keeps its merged cells. The fill step runs again on every refresh.

Will unmerging lose any data?

No. A merged block only ever held the top-left value, so unmerging puts that value in the first cell and leaves the rest empty, which is what you then fill down.

Related guides

• How to merge cells in Excel
• How to use Center Across Selection in Excel
• How to merge and unmerge cells with VBA
• How to combine text from multiple cells in Excel
• How to combine two columns in Excel