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

Excel Sort command just doesn’t work in the case of merged cells of different sizes. First of all, you need to unmerge them all. Or, you can make all the merged cells of the same size to sort them. In this article, you will learn 2 ways to sort merged cells of different sizes in Excel with ease.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


2 Ways to Sort Merged Cells of Different Sizes in Excel

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 sized.

Now let’s try to sort the state names alphabetically.

For that, select all the State names and then 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 first.

For that,

❶ Select all the merged cells again.

❷ Then 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:

❸ Now put your mouse cursor at the right-bottom corner of each of the cells having 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

❹ After that, select the range B5:E13.

❺ Then 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 hit the OK button.

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

Now the whole data table will be sorted based on Column B.

❼ Now merge each of the cells having the state names manually.


2. Use VBA Code to Sort Merged Cells of Different Sizes

You can use a VBA code to unmerge all the cells in a worksheet. Then you can sort all the cells and merge them back. That’s that you can sort merged cells of different sized.

Now follow the steps below to learn in detail.

❶ Press ALT + F11 to open the VBA Editor.

❷ Then, 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 of the cells in the selected area.
  • Then I used the Range and Select properties to make the selection in cell B5.

❹ Now go back to your worksheet and press ALT + F8.

The Macro dialog box will appear.

❺ Hit 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 instantly.

❻ Now put your mouse cursor at the right-bottom corner of each of the cells having the State names and double click on the Fill Handle icon to AutoFil.

❼ After that, select the Entire data table.Then go to Data >> Sort & Filter >> Sort.

The Sort dialog box will appear.

❽  Select Column B near the Sory by and hit the OK button.

Now the whole data table will be sorted based on Column B.

❾ Now merge each of the cells having the State names manually.


Practice Section

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


Conclusion

To sum up, we have discussed 2 ways to sort merged cells of different sized in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo