How to find and delete all merged cells in Excel 2013 worksheet

Random occurrences of merged cells is a sign of poorly formatted excel sheet. Merged cells are fine when we want to make excel sheet look pretty, but in case we need something very functional (e.g. sorting data in an entire column), it is essential to find and remove merged cells.

Following steps will help you to find and remove all merged cells:

1. Press Ctrl+F to open "Find and Replace" dialog box option

Ensure that "Find What:" field is empty.

2. Click on "Options" button in the dialog box

This will further expand the "Find and Replace" dialog box.

3. Click on the "Format" button >>> Go to "Alignment" tab >> "Text control" area

4. Place a check mark next to "Merge Cells" button by clicking it and then press "OK"

This will close the "Find Format" dialog box and " Find and Replace" dialog box gets activated.

5. Now click on "Find All" button to locate all the merged cells

Excel displays a list of all merged cells in the worksheet. Click an address in the list and the corresponding merged cell is activated.

REMOVING ALL MERGED CELLS

1. Click anywhere in the list window and press Ctrl+A

This will select all of the found occurrences of merged cells.

2. Go to "HOME" tab in ribbon >> "Delete" button >> Delete Cells [Shortcut: Alt+HDD]

This will remove all the merged cells from the worksheet. It will ask for option to choose the shifting of remaining cells.

IMPORTANT NOTE:

• In case the data is organized in certain manner and only deleting the cells do not work, you can also delete corresponding row or columns as below:

To delete rows containing merged cells:

Go to "HOME" tab in ribbon >> "Delete" button >> Delete Sheet Rows [Shortcut: Alt+HDR]

To delete columns containing merged cells:

Go to "HOME" tab in ribbon >> "Delete" button >> Delete Sheet Columns [Shortcut: Alt+HDC]