Excel tips: How to Locate Merged Cells in Microsoft Excel (and then get rid of it)
Do you use Excel to merge cells? If you answered yes, you should be aware of a few disadvantages of cell merging.
To begin, attempting to sort cells that have been merged will result in an error message that reads – To do this, all merged cells must be the same size.
To begin, this warning does not specify that the issue is due to merged cells; however, even if it did, it would be a massive task to unmerge these cells in order for sorting to work (especially if you have a huge data set).
Do not despair – there is a simple and quick way to locate the merged cell in Excel. You can easily unmerge, highlight, or delete all of these cells.
How to Locate Merged Cells in Microsoft Excel
In Excel, you can use the Find and Replace feature to locate merged cells.
Assume you have the following dataset:
As shown in the data above, cells A4 and A5 have been merged, as have cells A10 and A11. Now, if you attempt to sort this dataset, you will encounter the following 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.
- 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.
- In the Find and Replace dialogue box, click on the Options button. It will expand the dialogue box and show some additional options.
- In front of the “Find what” field, click on the Format button. It will open the Find Format dialogue box.
- In the Find Format dialogue box, select the Alignment tab and check the Merge Cells Option.
- Click OK.
- Click on Find All button. It will find all the cells that are merged and list it below the dialogue box.
- 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).
- Go to the Home tab and click on the Merge & Center button. This will instantly unmerge all the cells.
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.
