How to Find Merged Cells in Excel (& What You Can Do Instead)

You’ve probably come across situations where Excel didn’t allow you to make certain changes to your data because some of the cells you selected were merged. But, it can be tough to find merged cells in Excel because they often look so similar to others. 

Working with large datasets makes it even more difficult as there could be numerous merged cells scattered throughout the worksheet.

The easiest way to find merged cells (and unmerge them, if you wish) is by using Find & Replace. 

What can’t you do with merged cells?

Excel users often merge cells for formatting reasons. For example, they may want to create a label that spans several columns or center a title over an Excel dynamic chart.

However, the presence of merged cells will stop Excel from completing certain tasks, such as:

• Sorting cells or columns that contain merged cells
• Trying to reference a merged cell in a formula
• Copying merged cells, or a range of data that contains merged cells
• Counting cells in a range

Since merged cells restrict what you’re able to do, it’s generally advised not to use them unless you absolutely need to. Luckily, there’s a tool you can use to make your cells look merged without actually having to merge them – but more on that later!

First, here’s how to find merged cells in your worksheets. 

Find merged cells in Excel using Find & Replace

The Find & Replace tools are really useful for tasks like this. You’ll be able to highlight, select, and delete or unmerge cells in a few easy steps. Here’s how to do it!

Let’s say you have a dataset with a list of countries and their approximate populations like this: 

In this example, let’s merge cells B4 and B5, as well as cells C2 and C3. When cells are merged, only the upper left value is retained.

1. Select any single cell to search the whole worksheet, or select a specific range of cells to search within.

2. On the Home ribbon item, select Find and then Replace to open the Find & Replace dialog. 

3. Click Options to show additional features.

4. Click on the Format button to the right of the “Find what” field. The Find Format dialog box will be displayed.

5. Check the Merge Cells option under the Alignment. Hint: Be sure that you actually check the Merge Cells box. It may appear to be toggled on because the checkbox shows a dash by default, but it’s not active until you check it and it shows a check mark.

6. Click OK, and then Find Next or Find All.

7. If you selected Find All in the step above, the merged cells will be listed at the bottom of the dialog. You can select the first listing, hold down the Shift key, and then click the last listing to select each of the listed cells individually.

At this point, you can choose to highlight, delete, or unmerge the merged cells. 

• To highlight the merged cells – Select the style/color you’d like to highlight merged cells with on the Home ribbon tab
• To delete the merged cells – Simply right click and select Delete
• To unmerge the cells – Navigate back to the Home ribbon item, then click the Unmerge Cells button

How to format cells without merging them

Merged cells often become a nuisance, but they can make certain elements – chart titles, spreadsheet headings, etc. – look more clean and organized.

To replicate the look of merged cells without the limitations, you can use the Center Across Selection command instead. 

Let’s say you want to select two cells in adjacent columns (we’ll use cells B1 and C1 here). Select the cells you’d like to “merge,” then right-click the selection and choose the Format Cells option. 

Navigate to the Alignment tab, and choose Center Across Selection from the Horizontal options in the dialog box. After hitting OK, you’ll see that the two cells have the appearance of being merged 🙂

Do you have any other quick ways to find merged cells in Excel? Let us know in the comments!