Al Arafat Siddique
This article illustrates how to sort merged cells in Excel with step-by-step examples. The default sorting feature prevents sorting merged cells in a dataset. So, at first, we need to unmerge the cells sorting them. We’ll discuss two different methods here with detailed examples.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Quick and Easy Methods to Sort Merged Cells in Excel
Here we have a list of numbers that need to be sorted.
Let’s try to sort them from smallest to largest. To do so,
- Select the cells to be sorted.
- Then right–click the mouse.
- Hover on the Sort option and select Sort Smallest to Largest.
- Excel showed us a warning message as there are merged cells in the selected region.
Let’s find out how to solve the problem and sort the selected numbers.
1. Unmerge First to Sort Merged Cells in Excel
1.1 Single Column
To sort a range of cells that contain some merged cells, we need to unmerge them first. Then we’ll be able to sort them in any order. Let’s follow the steps to sort the column that we discussed in the previous section.
Steps: Part-1
- First, select the cells that we need to sort.
- Then, from the Home tab click on the Merge & Center to select Unmerge Cells option.
- After that, to find the blank cells after unmerging, click the Find & Select tab to choose the Go To Special
- In the Go To Special window, check the Blanks option and hit OK.
As a result, we can see the blank cells and the first one cell B6 is selected.
As a result, we can see the blank cells and the first one cell B6 is selected.
- In the formula bar, write =B5, the cell reference immediately above the selected blank cell B6, and press Ctrl + Enter.
- The above step filled the blank cells with numbers that were in the merged cells.
Steps: Part-2
- Finally, select the cells.
- Right-click the mouse.
- Hover on the Sort option to choose Sort Smallest to Largest.
- Finally, we successfully sorted the numbers in our desired order.
1.2 Multiple Columns
We’re going to sort a dataset with multiple columns based on the merged column i.e., the Region column.
Select the whole dataset and follow Step: Part-1 of section 1.1 to unmerge the merged cells and fill the blank cells. See the result in the following screenshot.
In this stage, we need to follow some easy steps to achieve our goal.
- Copy cells B5:B12 that are unmerged and filled by the above steps.
- Go to the Sort & Filter option and select Sort A to Z from the Home tab.
- Keep the default option in the warning dialogue box and hit OK to sort all data right to the Region column along with it.
- Now we have our dataset sorted based on the merged column.
Sort the Dataset Based on Column Other Than Merged Column
Let’s say we want to sort the following dataset based on the profit column. As we see that column B has merged cells in it, we cannot sort the dataset directly.
Select the whole dataset and follow Step: Part-1 of section 1.1 to unmerge the merged cells and fill the blank cells. See the result in the following screenshot.
Now we need to do the following.
- Copy cells B5:B12 that are unmerged and filled by the above steps.
- Paste them in the same position as Values only to remove the formulas.
- Now, select the profit column.
- In the Home tab, go to the Sort & Filter and select Sort Smallest to Largest
- Keep the default option in the warning dialogue box and hit OK to sort all data left to the Profit column along with it.
- Here is the sorted dataset.
Read More: How to Sort Multiple Columns with Excel VBA (3 Methods)
Similar Readings
- How to Sort Array with Excel VBA (Both Ascending and Descending Order)
- Use Excel Shortcut to Sort Data (7 Easy Ways)
- How to Sort by Ascending Order in Excel (3 Easy Methods)
- Random Sort in Excel (Formulas + VBA)
- Sort Column by Value in Excel (5 Methods)
2. Use VBA Code to Sort Merged Cells in Excel
2.1 Single Column
In this example, we took the same set of numbers that were used in method 1.1. This time we used VBA code to unmerge and then sort them in a specific order. The following steps will guide us to accomplish this.
Steps:
- Go to the Developer tab and select the Visual Basic option.
- In the Visual Basic window choose the Module option from the Insert tab to open a new module.
- Put the following code in the Visual Basic Editor and press F5 to run the code.
Sub Sort_Merged_Cells()
 Dim MyRange As Range
 Set MyRange = Range("A1:A9")
 On Error Resume Next
 With MyRange
 .UnMerge
 .Resize(.Rows.Count, 1).SpecialCells(xlBlanks). _
 Formula = "=R[-1]C"
 .Sort Key1:=.Range("A1")
 Range("VBA_RecFmt").Copy
 .PasteSpecial Paste:=xlPasteValues
 End With
End SubIn this code, we used A1:A9 as Range and A1 as the key to sorting the range.
The output we see is the sorted unmerged cells in column A.
2.2 Multiple Columns
In this example, we’ll sort a dataset with multiple columns. In the following dataset, the Region column has three merged cells.
Copy and then paste the following code in the Visual Basic Editor to sort the dataset based on the Region column in alphabetical order.
Sub Sort_Merged_Cells()
 Dim MyRange As Range
 Set MyRange = Range("A4:E11")
 On Error Resume Next
 With MyRange
 .UnMerge
 .Resize(.Rows.Count, 1).SpecialCells(xlBlanks). _
 Formula = "=R[-1]C"
 .Sort Key1:=.Range("A4")
 Range("VBA_RecFmt").Copy
 .PasteSpecial Paste:=xlPasteValues
 End With
End SubIn this code, we used A4:E11 as Range and A4 as the key to sorting the range.
The following screenshot shows the sorted dataset.
Read More: VBA to Sort Column in Excel (4 Methods)
Things to Remember
- Although using VBA code is great to accomplish our goal. But once the code is run, we lose the history. It means we cannot undo the change anymore.
- If we select the second option in the Sort Warning box, it’ll sort only the selected column cells, not the associated columns. That’s how we’ll lose the relation among the cells of a dataset. This option can be used in the case of sorting just one column that is not connected with others.
Conclusion
Now, we know how to sort merged cells in Excel. Hopefully, it would encourage you to use this more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
