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

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.

Sort Merged Cells in Excel

Let’s try to sort them from smallest to largest. To do so,

  • Select the cells to be sorted.
  • Then rightclick the mouse.
  • Hover on the Sort option and select Sort Smallest to Largest.

Sort Merged Cells in Excel

  • Excel showed us a warning message as there are merged cells in the selected region.

Sort Merged Cells in Excel

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.

Sort Merged Cells in Excel

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

Sort Merged Cells in Excel

  • In the Go To Special window, check the Blanks option and hit OK.
  • Sort Merged Cells in ExcelAs a result, we can see the blank cells and the first one cell B6 is selected.

Sort Merged Cells in Excel

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

Sort Merged Cells in Excel

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.

Sort Merged Cells in Excel


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.

Sort Merged Cells in Excel

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.

Sort Merged Cells in Excel

  • Keep the default option in the warning dialogue box and hit OK to sort all data right to the Region column along with it.

Sort Merged Cells in Excel

  • Now we have our dataset sorted based on the merged column.

Sort Merged Cells in Excel

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.

Sort Merged Cells in Excel

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.

Sort Merged Cells in Excel

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

Sort Merged Cells in Excel

Read More: How to Sort Multiple Columns with Excel VBA (3 Methods)


Similar Readings


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 Range and A1 as the key to sorting the range.

Sort Merged Cells in Excel

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.

Sort Merged Cells in Excel

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.

Sort Merged Cells in Excel


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.


Related Articles

Al Arafat Siddique

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

Leave a reply

ExcelDemy
Logo