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.
- Microsoft Excel will show a warning box saying, “To do this, all the merged cells need to be the same size.”
- 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.
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.
- Select the range B5:E13.
- Go to Data >> Sort & Filter >> Sort.
The Sort dialog box will appear.
- Select Column B near the Sort 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 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
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.
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.
Download the Practice Workbook
You can download the Excel file from the following link and practice.
Related Articles
- How to Sort by Name in Excel
- How to Sort by Last Name in Excel
- How to Sort Duplicates in Excel
- How to Sort Numbers in Excel
- How to Put Numbers in Numerical Order in Excel
- How to Sort in Excel by Number of Characters
- How to Arrange Numbers in Ascending Order in Excel Using Formula
<< Go Back to Sort in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
