How-To

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

spreadsheet-merge-cells-hero

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

excel merged cells error

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.
    excel select all
  2. Click on the Home tab.
    excel home
  3. Click on the Find & Select icon.
    excel find and select
  4. Select Find.
    excel find
  5. Click on the Options button.
    excel find options
  6. At the end of the Find What settings, click on Format.
    excel find 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.
    excel find merged cells
  8. Click Find All.
    excel 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.
    excel find results

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.
    excel select all find results
  3. In the Home tab, click on the down arrow next to the Merge & Center tool.
    excel merge and center
  4. Select Unmerge Cells.
    excel 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.
    excel merge and center mac
  3. Select Unmerge Cells.
    excel unmerge cells mac
  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.

Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top