How to Sort Merged Cells of Different Sizes in Excel (2 Easy Ways)

Method 1 – Sort Merged Cells of Different Sizes Using Unmerge Cells and Sort Commands

The data table in the following picture has merged cells of different sizes.

STEPS:

Select all the State names and click on the A to Z icon in the Sort & Filter group on the Data tab.

Sort Merged Cells of Different Sizes Using Unmerge Cells and Sort Command

Microsoft Excel will show a warning box saying, “To do this, all the merged cells need to be the same size.”

Warning Box: Sort Merged Cells of Different Sizes Using Unmerge Cells and Sort Command

To fix this problem, we need to unmerge all the cells.

❶ Select all the merged cells again.

❷ Go to Home >> Alignment >> Merge & Center >> Unmerge Cells.

Unmerge Cells: Sort Merged Cells of Different Sizes Using Unmerge Cells and Sort Command

All the cells will be unmerged, and it will look like this:

Place your mouse cursor at the right-bottom corner of each cell with the State names and double-click on the Fill Handle icon to AutoFil.

Fill Handle: Sort Merged Cells of Different Sizes Using Unmerge Cells and Sort Command

Select the range B5:E13.
Go to Data >> Sort & Filter >> Sort.

Sort Merged Cells of Different Sizes Using Unmerge Cells and Sort Command

The Sort dialog box will appear.

Select Column B near the Sort by and press OK.

The Sort dialog box: Sort Merged Cells of Different Sizes Using Unmerge Cells and Sort Command

The whole data table will be sorted based on Column B.

Merge each of the cells with the state names manually.

Read More: How to Sort Merged Cells in Excel

Method 2 – Use VBA Code to Sort Merged Cells of Different Sizes

STEPS:

Press ALT + F11 to open the VBA Editor.
Go to Insert >> Module.

Insert the following code in the VBA Editor.

Sub UnmergeCells()
    Range("B5:D13").Select
    Selection.UnMerge
    Range("B5").Select
End Sub

Use VBA Code to Sort Merged Cells of Different Sizes

Breakdown of the Code

Here, I specified a range to select.
Then, I used the UnMerge property to unmerge each cell in the selected area.
Then, I used the Range and Select properties to make the selection in cell B5.

Go back to your worksheet and press ALT + F8.

The Macro dialog box will appear.

Press the Run button for now.

Macro dialog box: Use VBA Code to Sort Merged Cells of Different Sizes

All the merged cells will be unmerged.

Put your mouse cursor at the right-bottom corner of each cell with the State names and double-click on the Fill Handle icon to AutoFil.

Select the Entire data table.
Go to Data >> Sort & Filter >> Sort.

The Sort dialog box will appear.

Select Column B near the Sory by and press OK.

The whole data table will be sorted based on Column B.

Merge each of the cells with the state names manually.

Read More: How to Sort Unique List in Excel

Practice Section

At the end of the provided Excel file, you will get an Excel sheet like the following screenshot, where you can practice all the methods discussed in this article.