Formatting Issues

How to Fix Excel Sorting Error: Find and Unmerge Cells

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to find and unmerge irregularly sized cells to resolve an error that prevents data sorting in Excel.

Product: Excel
Device & OS: not provided
Scenario: Sorting a data range in a spreadsheet, typically after converting a document like a PDF to Excel, which often introduces inconsistent merged cells.
Observed behavior: Excel displays the error message 'All merged cells must be the same size' and fails to execute the sort operation.

Before you start

Before unmerging your cells and changing the layout, add a temporary index column (numbering your rows 1, 2, 3, etc.) so you can easily restore the original row order later if needed.

Solution 1Recommended

Unmerge Cells to Enable Sorting

Remove merged formatting from the selected data range so that Excel can sort the uniform individual cells without triggering dimension errors.

Excel requires all cells within a sorting range to have uniform dimensions. If your data contains merged cells of varying sizes, the sort command will be blocked. By unmerging the cells, you create a standard grid that Excel can easily organize.

Select the Affected Range

Highlight the specific range of cells you want to sort, or press Ctrl+A to select the entire worksheet.

Navigate to the Alignment Tools

Go to the 'Home' tab located on the top Excel ribbon.

Unmerge the Cells

Locate the 'Alignment' group, click on the 'Merge & Center' dropdown arrow, and select 'Unmerge Cells' from the list.

Apply Your Sort

With the cells now uniformly separated, go to the 'Data' tab and click 'Sort' to organize your data.

Original Order Protected: If you added an index column before unmerging, you can safely experiment with sorting, knowing you can always sort by the index column to revert to your original layout.

WPS Spreadsheet Solution

Sort Data and Manage Merged Cells Effortlessly in WPS Office

WPS Spreadsheet provides a highly compatible and intuitive interface for managing complex data sets. If you frequently encounter formatting issues from converted files, WPS Office handles cell alignments, merging, and unmerging smoothly.

1. Open Your Spreadsheet: Launch WPS Office and open the spreadsheet file containing the sorting error.
2. Select the Data: Highlight the entire data range that includes the inconsistently merged cells.
3. Unmerge Cells: Navigate to the 'Home' tab, click the 'Merge and Center' icon, and choose 'Unmerge Cells'.
4. Sort the Data: Go to the 'Data' tab on the ribbon and click 'Sort' to correctly organize your rows.

Easily find and unmerge irregularly sized cells to instantly fix sorting errors.Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Built-in PDF to Excel converter that minimizes messy merged cell layouts.Free, lightweight, and available on Windows, Mac, Linux, iOS, and Android.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel say 'All merged cells must be the same size'?

This error occurs when you attempt to sort a range that contains merged cells of varying dimensions. Excel requires uniform cell sizes across rows and columns to accurately execute sorting logic.

How can I quickly find all merged cells in my worksheet?

You can locate them using the Find feature. Press Ctrl+F, click 'Options', then click 'Format'. Under the 'Alignment' tab, check the 'Merge cells' box and click 'Find All' to see a list of every merged cell.

Will unmerging cells delete any of my data?

No, your data is safe. When you unmerge a cell block, the content from the merged cell is simply placed into the upper-leftmost individual cell of that separated block. The other newly unmerged cells will remain blank.

Can I sort data without unmerging the cells?

Generally, no. Standard sorting requires identically sized cells. You must unmerge the cells first. If you need the layout to look a certain way, you can copy the original data to empty cells or use 'Center Across Selection' as an alternative to merging.

Document & File Issues