Why does Excel say all merged cells need to be the same size?

Excel requires every cell in a sort range to occupy exactly one row and one column. When any cell is merged across multiple rows or columns, Excel cannot rearrange the rows and shows this error.

Can I sort Excel data without unmerging cells?

No. Excel's sort feature requires uniform cell sizes. The only working approach is to unmerge cells first, fill the blank cells that result, sort, then re-merge if needed.

How do I find all merged cells in an Excel sheet?

Press Ctrl+F to open Find, click Options, then click the Format button and go to the Alignment tab. Check 'Merge cells' and click Find All — Excel will list every merged cell in the sheet.

Does this merged cells error appear in Excel for Mac?

Yes, the same error occurs in Excel for Mac. To unmerge on Mac, go to the Format menu, select Cells, open the Alignment tab, and uncheck the Merge cells checkbox.

What is a better alternative to merged cells in Excel?

Use Center Across Selection instead of Merge and Center. Select the cells, open Format Cells (Ctrl+1), go to the Alignment tab, and set Horizontal to Center Across Selection. It looks identical to a merge but keeps each cell independent, so sorting and formulas work normally.

Why do I get the merged cells error when copying a formula down?

If the destination cells are a different size from the source merged cell, Excel cannot copy the formula there. Unmerge the source cell first, or use Ctrl+D or Ctrl+C / Ctrl+V to copy the formula value without dragging the fill handle.

All the Merged Cells Need to be the Same Size

Updated: April 2026 · Reviewed for accuracy on Excel 365

I see this error most often when clients send me spreadsheets with merged header rows, you try to sort the data and Excel stops you with: "To do this, all the merged cells need to be the same size." It looks like a formatting complaint, but the real fix has nothing to do with sizes.

In this tutorial, I'll show you exactly why this happens and 5 ways to fix it — whether you're sorting data, copying formulas down, or looking to avoid merged cells altogether.

Excel 365 Excel 2021 Excel 2019 Excel 2016 Excel Online — limited Excel for Mac — different path

Why I'm Getting this Error of "all the merged cells need to be the same size"

In Excel, when you have merged cells in the data and need to sort it, you will face an error that says, "To do this, all the merged cells need to be the same size."

This error might sound like you need to adjust all the merged cells' sizes (height and width). But the real problem here is the merged cells, not their size.

Excel error dialog — all the merged cells need to be the same size

The above example shows that the third value is in a merged cell. The error in the message box says that merged cells must be the same size.

Apart from this, you will get the same error when you have a formula in a merged cell and try to copy that formula to the cell downwards.

Formula in a merged cell in Excel — fill handle dragging causes merged cells error

If you try to use the fill handle to drag down the formula to the next cell, it will copy the formula and merge the next cell(s).

Microsoft documents this behaviour officially

How to Fix this Problem of "all the merged cells need to be the same size"

The only way to fix this error of "To do this, all the merged cells need to be the same size" is to fix the merged cells in the data.

1. Fix the Sorting Problem

As I said, to sort this problem, you need to fix the merge cells that merged in the data. So for this, all you need to do is:

First, you need to select the merged cells from the data. And if you have multiple merged cells in the data, you can select the entire data.
After that, go to the "Home" tab > and in the merged cells drop-down, select the "Unmerge Cells" option.
This will unmerge the cells, and you can sort the data now. But there is one thing that you need to do: remove the blank cells. You can delete all those blank cells, one by one (if you have less), but if there are multiple blank cells left, select the data, and then go to the Home Tab > Find and Select > Go To Special.
Now, when you click on the option, select blank in the dialog box and click OK. This will select all the blank cells from the data.
In the end, right-click and delete all the blank cells in one go.

After that, you can sort the data the way you want. You won't get the error this time.

2. Fixing the Copy Formula Problem

The fix to this problem is the same as we have done with the sorting problem. You must unmerge the merged cells first, then manually remove the blank cells or use the Go To Special option.

Or, if you don't need to remove the blank cells, you can copy your formula or value to the cells downward using the Ctrl + D or the fill handle.

3. Fix with a VBA Macro (Unmerge and Fill in One Click)

If you have a large dataset with many merged cells spread across it, unmerging them one by one is slow. This VBA macro unmerges every merged cell in your selection and automatically fills the blank cells left behind — all in one click.

To use it, press Alt + F11 to open the VBA editor, go to Insert → Module, paste the code below, and run it with F5 after selecting your data.

 Sub UnmergeFillCells() Dim cell As Range Dim mergeArea As Range Dim firstVal As Variant For Each cell In Selection.Cells If cell.MergeCells Then Set mergeArea = cell.MergeArea firstVal = mergeArea.Cells(1, 1).Value mergeArea.UnMerge mergeArea.Value = firstVal End If Next cell MsgBox "Done! All merged cells have been unmerged and filled." End Sub 

After running the macro, your data will have no merged cells and no blank gaps — you can sort immediately.

4. Use "Center Across Selection" Instead of Merging

This is the best long-term fix. Center Across Selection looks exactly like a merged cell — the text is centred across multiple columns — but each cell stays independent, so sorting and formulas work without any errors.

Select the cells you want to centre across (e.g. A1:C1).
Press Ctrl + 1 to open Format Cells.
Go to the Alignment tab.
In the Horizontal drop-down, select Center Across Selection.
Click OK.

Tip: If you already have merged cells in your data, unmerge them first, then apply Center Across Selection. This gives you the same visual result with none of the sorting or formula problems.

5. Fix on Excel for Mac

The error appears in Excel for Mac too. The fix is the same — unmerge the cells — but the path through the ribbon is slightly different on Mac.

Select the merged cells or the entire data range.
Go to the Format menu at the top of your screen.
Click Cells.
Open the Alignment tab.
Uncheck the Merge cells checkbox and click OK.

Excel for Mac note: The Home tab ribbon also has a Merge and Center button on Mac — click its drop-down arrow and select Unmerge Cells for a faster route.

About the Author

Puneet Gogia Microsoft MVP — Excel

Founder, Excel Champs

I've been working with Excel since college and writing about it since 2015. Before starting Excel Champs, I spent years as a data analyst — using Advanced Excel, VBA, and pivot tables in real manufacturing and forecasting environments. Everything on this page has been written and tested by me personally.

3M+

Readers helped

1,000+

Tutorials written

Since 2015

Running Excel Champs

About me LinkedIn Twitter / X MVP Profile