How to sort data with merged cells in Excel?

Attempting to sort data in Excel that contains merged cells often results in an error prompt, as shown in the screenshot on the left. This behavior is a result of Excel’s current limitations—Excel does not natively support sorting ranges with merged cells, as merged ranges disrupt the underlying data structure and make it difficult for the sort function to operate normally.

When working with worksheets that use merged cells for formatting or grouping data, you may still need to organize or reorder your data. To accomplish this, you must first handle those merged cells appropriately. Below, you will find several practical methods for handling and sorting data with merged cells while preserving your information.

Sort data with merged cells by unmerging all cells first
Sort data with merged cells by Kutools for Excel
Sort data with merged cells by VBA automation (unmerge, fill, sort, re-merge)

Sort data with merged cells by unmerging all cells first

Because Excel’s default sort function cannot operate on ranges with merged cells, the necessary approach is to unmerge all merged cells within your data list first. Once unmerged, blanks will appear where merged cells were, so you will need to fill these empty cells to maintain accurate sorting. Here is the step-by-step method:

1. Select the full list or table containing merged cells that you would like to sort. With the data selected, click Home > Merge & Center to unmerge all merged cells. Please refer to the screenshot below for visual guidance:

2. After unmerging, you’ll notice that the previously merged areas are now blank cells. While the merged cells are still selected, proceed to the Home tab and click on Find & Select > Go To Special. This step ensures efficient bulk selection of all blank cells within your range, making the next step much quicker.

3. In the Go To Special dialog box, choose the Blanks option and click OK. This will immediately highlight all blank cells in your selected range.

4. With blanks still highlighted, go to the Formula Bar and type the = symbol, then select the cell directly above the first blank cell to create a reference formula (for example, if C3 is blank, input =C2). Instead of pressing just Enter, hold Ctrl and press Enter to fill all the selected blanks with their respective above-cell values at once.

After this step, every former merged area is populated with its original value, ensuring your data integrity during sorting.

5. You are now ready to sort your data as normal—select any column, go to the Data tab, and choose Sort A to Z or Sort Z to A. If you wish to restore the merged formatting after sorting, you may manually re-merge adjacent identical values, but take care to avoid merging across unrelated data.

Tip: Before proceeding, always make a backup of your original data if retaining the merged format is important. Also be aware that after unmerging and filling blanks, formulas will be present. If you’d like to convert formulas to values, select the range, copy, and then paste as values to avoid unwanted formula updates after sorting.

Sort data with merged cells by Kutools for Excel

Kutools for Excel provides a more straightforward and efficient solution for sorting data with merged cells, making the process much more reliable when dealing with large datasets or frequent formatting requirements. The Unmerge Cell & Fill Value utility in Kutools for Excel can automatically unmerge cells and fill the resulting blanks with the original merged value, eliminating manual intervention and significantly improving efficiency.

1. Highlight the range that contains the merged cells you want to sort. Next, navigate to Kutools > Merge & Split > Unmerge Cell & Fill Value. Refer to the following screenshot for guidance:

Upon executing this operation, all merged cells within the selected range are promptly unmerged, and the newly created blank cells are filled with the corresponding value from the previously merged cell, ensuring consistency throughout the table.

2. You may now sort your data as usual. After sorting, for a consistent appearance or for data presentation purposes, Kutools also provides a quick way to re-merge cells with the same content. Simply select the sorted range, then go to Kutools > Merge & Split > Merge Same Cells to efficiently reapply merged formatting only where values are identical.

Pros: This approach minimizes manual steps, reduces the likelihood of errors, and is especially suitable when working with complex tables or regular reporting needs. Always ensure you review the merged output after sorting to verify that merging does not exclude any important data relationships.

Sort data with merged cells by Kutools for Excel

Sort data with merged cells by VBA automation (unmerge, fill, sort, and re-merge)

For users familiar with macros, automating the process via VBA can streamline the handling of merged cells—especially with repetitive or large-scale sorting tasks. This approach can efficiently:

• Unmerge all merged cells in a specified range
• Fill resulting blank cells with the value from above for consistency
• Sort the data by any specified column
• Optionally, re-merge consecutive identical values in the sorted data

This method is flexible for customized workflows but requires enabling macros and careful operation to avoid data loss. If you’re new to VBA, consider testing on a sample sheet first.

Operation steps:

1. On the Ribbon, click Developer > Visual Basic to open the Visual Basic for Applications window, then click Insert > Module. Copy and paste the following code into the Module window:

Sub SortDataWithMergedCells()
    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Dim lastRow As Long, lastCol As Long
    Dim sortCol As Variant
    Dim reMerge As VbMsgBoxResult
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    Set rng = Application.InputBox("Select the data range to sort", xTitleId, Selection.Address, Type:=8)
    
    If rng Is Nothing Then Exit Sub
    
    ' Store dimensions
    lastRow = rng.Rows.Count + rng.Row - 1
    lastCol = rng.Columns.Count + rng.Column - 1
    
    ' Unmerge and fill down values
    rng.UnMerge
    For Each cell In rng
        If IsEmpty(cell.Value) Then
            cell.Value = cell.Offset(-1, 0).Value
        End If
    Next cell
    
    ' Ask for sort column
    sortCol = Application.InputBox("Enter column number in your selection to sort by (e.g. 1 for first column)", xTitleId, 1, Type:=1)
    
    If sortCol = False Then Exit Sub
    
    ' Sort the range
    rng.Sort Key1:=rng.Cells(1, sortCol), Order1:=xlAscending, Header:=xlNo
    
    ' Ask if user wants to re-merge identical consecutive values
    reMerge = MsgBox("Do you want to re-merge identical consecutive values in the sorted range (column " & sortCol & ")?", vbYesNo + vbQuestion, xTitleId)
    
    If reMerge = vbYes Then
        Dim startCell As Range, endCell As Range
        Dim currVal As Variant
        Dim i As Long
        
        Set startCell = rng.Cells(1, sortCol)
        currVal = startCell.Value
        Set endCell = startCell
        
        For i = 2 To rng.Rows.Count
            If rng.Cells(i, sortCol).Value = currVal Then
                Set endCell = rng.Cells(i, sortCol)
            Else
                If startCell.Address <> endCell.Address Then
                    ws.Range(startCell, endCell).Merge
                End If
                Set startCell = rng.Cells(i, sortCol)
                currVal = startCell.Value
                Set endCell = startCell
            End If
        Next i
        
        ' Final group
        If startCell.Address <> endCell.Address Then
            ws.Range(startCell, endCell).Merge
        End If
    End If
    
    On Error GoTo 0
End Sub

2. To run the macro, click the Run button in the VBA editor. You will be prompted to select the data range and the sorting column number. Confirm each prompt and let the macro complete all steps. If you choose to re-merge, the code will automatically merge adjacent cells with the same value in the designated column.

Tips:

• Always make a backup of your worksheet before running VBA macros, as actions like unmerging/merging are irreversible.
• Sorting by header or non-numeric columns may require extra adjustments—be sure to specify the correct column number as indicated in your selection.
• If you encounter errors after sorting, verify entire row or column references, and adjust the macro's parameters as needed for your specific data layout.

Advantages: Automates an otherwise repetitive, multi-step task—especially useful for recurring work.
Limitations: VBA macros require enabling macros (potential security warning), and complex table layouts may need manual review after merging is restored.

For best results, test this macro on a duplicate of your data first. This VBA solution is particularly useful for users who frequently need to repeat the sort-and-merge process or work with dynamic data exports.