# How to Sort Merged Cells in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

## How to Sort Merged Cells in Excel (2 Easy Methods)

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 the Unmerge Cells option.

• After that, to find the blank cells after unmerging, click the Find & Select tab to choose the Go To SpecialÂ feature.

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

• 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 Duplicates in Excel

### 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 Sub``````

In this code, 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

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

Read More: How to Sort Unique List in Excel

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

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF