How-To

How to Find Merged Cells in Excel (And Remove Them)

By Adam Davidson

Published August 2, 2022

If you’re sorting data in Excel, you may get an error if it contains merged cells. Learn how to find merged cells in Excel and remove them.

You’re trying to sort some of your data in Excel. Everything seems fine until you get a nasty error: “To do this, all the merged cells need to be the same size.”

Merged cells can cause havoc when you’re trying to sort your data. The trouble is, merged cells aren’t always that easy to spot. The good news is that it’s easy to hunt down any rogue merged cells and get rid of them. Well, it’s easy if you’re using Windows; Mac users have a harder time with things.

Here’s how to find merged cells in Excel and remove them.

How to Find Merged Cells in Excel on Windows

Before removing merged cells from your data, you need to find them all. This is simple to do using Excel’s Find and Replace tool.

To find merged cells in Excel:

1. Highlight the cells where you want to look for merged cells. To select all cells, click the empty space at the top left of your spreadsheet or press Ctrl+A.
   
2. Click on the Home tab.
   
3. Click on the Find & Select icon.
   
4. Select Find.
   
5. Click on the Options button.
   
6. At the end of the Find What settings, click on Format.
   
7. Under the Alignment tab, click on Merged Cells. It should contain a checkmark rather than a line. Click OK to confirm the Format settings.
   
8. Click Find All.
   
9. All merged cells will be listed at the bottom of the window. Clicking on a result will take you to that cell.
   

How to Unmerge Cells in Excel on Windows

Now that we’ve found them, you’ll need to learn how to remove merged cells in Excel.

To unmerge cells in Excel:

1. Follow the steps above to find all of the merged cells in your selection.
2. Select all the found cells by clicking on the top result, holding Shift, and clicking on the bottom result. This will select all of the merged cells that were found.
   
3. In the Home tab, click on the down arrow next to the Merge & Center tool.
   
4. Select Unmerge Cells.
   
5. Your cells are now unmerged.

How to Find and Fix Merged Cells in Excel for Mac

Frustratingly, if you’re using a Mac, there’s no simple way to find merged cells in Excel. When using the Find and Replace tool, the Format option does not exist, so you are unable to search for cells based on them being merged. Since you can’t search for merged cells easily, removing all merged cells in your sheet is the only option.

To remove merged cells in Excel for Mac:

1. Select the range of cells you want to remove merged cells from or select all cells by clicking in the empty space at the top left of your spreadsheet or press Cmd+A.
2. In the Home tab, click on the down arrow next to the Merge & Center icon.
   
3. Select Unmerge Cells.
   
4. All of the cells in your selected data will be unmerged.

Troubleshooting in Excel

Learning how to find merged cells in Excel can help you fix errors when sorting your data.

If you’re having other issues, help is at hand. Learn how to find and remove circular references in Excel or how to fix Excel if it keeps crashing. If you’ve had enough of being told that there’s something wrong with your spreadsheet, you can even ignore all errors in Excel.