How to Sort Merged Cells in Excel: 2 Easy Methods

Method 1 – Unmerge First to Sort Merged Cells in Excel

1.1 Single Column

Steps: Part-1

Select the cells that we need to sort.
Click on the Merge & Center from the Home tab to select the Unmerge Cells option.

Sort Merged Cells in Excel

Find the blank cells after unmerging, and click the Find & Select tab to choose the Go To Special feature.

Sort Merged Cells in Excel

In the Go To Special window, check the Blanks option and hit OK.

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 in the merged cells.

Steps: Part-2

Select the cells.
Right-click the mouse.
Hover on the Sort option to choose Sort Smallest to Largest.

We successfully sorted the numbers in our desired order.

1.2 Multiple Columns

We will 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. The result is shown in the following screenshot.

We must 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.

We have our dataset sorted based on the merged column.

Sort the Dataset Based on Column Other Than Merged Column

To sort the following dataset based on the profit column. 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. The result is shown 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.

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.

This is the sorted dataset.

Method 2 – Use VBA Code to Sort Merged Cells in Excel

2.1 Single Column

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 Sub

We used A1:A9 as the 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

We’ll sort a dataset with multiple columns. In the following dataset, the Region column has three merged cells.

Copy and 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 Sub

In this code, we used A4:E11 as Range and A4 as the key to sorting the range.

The following screenshot shows the sorted dataset.

Things to Remember

Although using VBA code is great for accomplishing 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.