How to Find Merged Cells in Excel (and then get rid of it)

Do you merge cells in Excel? If yes, you need to know a few drawbacks of merging cells.

To begin with, if you try and sort cells that have been merged, it will refuse to do so and show you an error that says – To do this, all the merged cells need to be of the same size.How to find merged cells in Excel - Sort Error

OHHH BUMMER!

First of all, this warning does not specify that the issue is because of merged cells, but even if you know that there would be a huge task to unmerge these cells for sorting to work (especially if you have a huge data set).

See Also: How to merge cells in Excel the right way.

Don’t lose heart – there is a quick and easy way to find the merged cell in Excel. You can easily select all these cells and unmerge it, or highlight it, or remove it.

How to Find Merged Cells in Excel

You can use the Find and Replace feature to find merged cells in Excel.

Suppose you have a dataset as shown below:

How to find merged cells in Excel - Data Set

As you can see in the data above, cell A4 and A5 have been merged and cell A10 and A11 have been merged. Now, if you try to sort this dataset, you will get an error as shown below:

How to find merged cells in Excel - Sorting Error

The only way to resolve this is to find these merged cells and unmerge it.

Here are the steps to do it:

  • Select the cells in which you want to find the merged cells.How to find merged cells in Excel - Select Data Set
  • Go to Home –> Editing –> Find & Select –> Replace (You can also use the keyboard shortcut – Control + H). It will open the Find and Replace dialogue box.How to find merged cells in Excel - Replace
  • In the Find and Replace dialogue box, click on the Options button. It will expand the dialogue box and show some additional options.How to find merged cells in Excel - Find and Replace Options
  • In front of the “Find what” field, click on the Format button. It will open the Find Format dialogue box.How to find merged cells in Excel - Format button
  • In the Find Format dialogue box, select the Alignment tab and check the Merge Cells Option.How to find merged cells in Excel - Merge Cell Check Box
  • Click OK.
  • Click on Find All button. It will find all the cells that are merged and list it below the dialogue box.How to find merged cells in Excel - Find ALL
  • Select all the listed cells (select the first listing, press Shift and then select the last one. This will select all the listings at once).How to find merged cells in Excel - Found Cells
  • Go to the Home tab and click on the Merge & Center button. This will instantly unmerge all the cells.How to find merged cells in Excel - Merge and Center

Now you can sort the data as it would not have any merged cells. Instead of merging these cells, you can also delete it or highlight it. To do this, follow all the steps given above, and in the last step, instead of unmerging the cells, highlight it or delete it.

You May Also Like the Following Excel Tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Picture of Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

5 thoughts on “How to Find Merged Cells in Excel (and then get rid of it)”

  1. Thanks, Sumit – nice article.

    I was wondering – can’t you just select the entire sheet and unmerge if the idea it simply unmerge? I tried and it worked for me. Although, I reckon highlighting and deleting might require the approach described in this article.

    I came here looking for a way to unmerge cells but also populating those cells with information from the merged cells. Is there an easy way to accomplish that?

    Reply

Leave a Comment

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster