How to Find and Unmerge Merged Cells in Excel

Merged cells can pose a problem when you need to sort data in a specific way. For example, if you try to sort a range that contains merged cells, you will get an error: “This operation requires merged cells to be the same size.”

If you have too many merged calls on your worksheet, you probably don’t want to manually separate each cell and then try to sort the data. Luckily, the Excel search function is powerful enough to find and select all merged cells on a worksheet. Once merged cells are selected, you can unmerge them with a single click.

Note that it is easy to unmerge all cells. However, you must use the Find Function when you only want to deselect selected cells in a worksheet without deselecting them one by one.

Disconnect all merged cells in Microsoft Excel

Separating all the cells is pretty easy. Press Ctrl + AClick on that home tab, then the Merge & center icon and then select separate cells.

If you’re using a Mac, this is the only method you can use. The method described in the next section only works on Windows.

Split selected cells in Microsoft Excel for Windows

This method is helpful when you only want to separate selected cells, especially when you have hidden the gridlines on your worksheet, which makes detecting merged cells a nightmare.

The process involves two steps. The first step is to find and select all merged cells in your worksheet using the search function in Excel. The second step is to select the cells you want to split from the merged cells list and split them.

Step 1: Find merged cells

First, select the cell range where you want to search for merged cells. The easiest way is to press Ctrl + A and select all cells, assuming you don’t just want to target a specific part of the worksheet. Don’t worry if the selection contains cells that you don’t want to unmerge. You can later exclude them from your selection.

After selecting the cells, press Ctrl + F. Alternatively, you can also click on home Tab at the top left of the Excel window. Then click the Search & select option in the editing Group of tools from the ribbon above and select Find.

press the options button in the Search and replace Window. When the options appear, click format Button.

In which find format window, select the alignment Check the box to the left of connecting cellsand click OK.

When returning to the Search and replace window, click find all Button. You will see all merged cells listed at the bottom of the window.

Step 2: Select and separate cells

Now that you have the complete list, you can select the cells to unmerge. Hold down the ctrl button and click on the merged cells you want to select. Alternatively, you can also press and hold change button and click a merged cell from the list. This selects that cell and all cells above it in the list.

After selecting the cells, go back to home tab in Microsoft Excel and click the Merge & center icon from the alignment group of tools. Choose Unmerge cells.

Separating cells made easy

Hopefully you were able to separate selected cells on your worksheet and sort the data to your liking. Microsoft Excel’s Find feature lets you search for a variety of items in the worksheet and apply changes at scale.

However, the search function is not the only practical function. If you use Microsoft Excel regularly, you should learn about its advanced features to save time and increase your productivity.