Sorting error (all merged cells must be the same size

Question

I am trying to sort customer information that has columns that I have used the merged and center function. When I try to sort the customer list alphabetically, it says I can't do it unless all merged cells are the same size. Is there a way to see which cells it is having problems with. It looks like each column's merged cells are the same.

Thank you for any help I can get.

Cheryl Smith

Answer 1

Hi, @Cheryl Smith

This usually happens when at least one merged area in the range is a different size. It may not be obvious just by looking at the sheet.

To help narrow down the issue:

1. Are the merged cells only in the column headings, or are there merged cells within the actual customer rows you are trying to sort?
2. Do any of the merged cells span multiple rows, or are they only merged across columns?
3. If possible, can you make a copy of the worksheet, unmerge the cells in the customer list, and check whether sorting then works?

For a list that needs to be sorted regularly, it is generally better not to use merged cells within the data itself. You can often use Center Across Selection instead if the merging is only for appearance.

Click "Add comment" to let me know.

Thanks!

Answer 2

Yes. That error means the sort range contains merged cells that are not uniform. Excel can sort only when either:

• there are no merged cells in the range, or
• every merged cell in the range occupies the same number of rows and columns

A practical way to find problem cells is to locate all merged cells first:

1. Go to Home > Editing > Find & Select > Find.
2. Select Options > Format > Alignment.
3. Select merged cells, then select OK.
4. Select Find All.
5. Review the listed cells and jump to each merged area.

If the merged areas are not all the same size, sorting will fail.

To fix it, use one of these approaches:

1. Unmerge all merged cells in the sort range
   • Select the full range to be sorted.
   • On Home, in the Alignment group, open the Alignment dialog box launcher.
   • On the Alignment tab, clear Merge cells.
   • Select OK.
2. Make all merged cells the same size
   • Each merged cell in the sort range must span the same number of rows and columns.
   • For example, if one merged area is 2 columns wide, the other merged areas in that sort range must also be 2 columns wide.

Important: unmerging can change the layout of the data. If the sheet structure is important, make a copy first.

Also make sure the entire customer list is selected before sorting. Sorting only part of a larger range can cause unexpected results.

---

• You might receive an error message when you sort a range that contains merged cells in Excel
• Error when trying to sort an Excel file - Microsoft Q&A
• Sort data in a range or table in Excel