Merged cells can often be quite hard to spot, and yet can have major unwanted impacts on your attempts to sort, calculate, and rearrange data in any given cell group. To find them, however, is relatively straightforward.
To learn how to find merged cells in Excel, simply follow our tutorial below. To learn what to do once you’ve found merged cells, and for all other information and queries, please direct your attention to our FAQs at the end of the article.
Table of Contents
How to find merged cells in Excel
- Open the Excel application on your device, and load the document in which you think you have merged cells
- Next, highlight all of the cells in which you believe there to be merged cells, simply by clicking and dragging from top-left to bottom-right to cover the entire area.
- Now you want to press CTRL+H on your keyboard, which brings up the ‘Find & Replace’ function.
- Click on the ‘Options’ button below the two blank fields
- Next, click the ‘Format’ button to the right of the ‘Find what’ field
- In the pop-up window, click the ‘Alignment’ tab
- Now, locate and check the blank box next to ‘Merge Cells’ under ‘Text Control’
- Finally, click ‘OK’, and then ‘Find All’ back in the Find & Replace function window. Doing so will produce a list of all of the merged cells hidden within your highlighted selection of data. You can click on each individual one and you will see it highlighted further within your dataset.
And there it is: that’s all there is to finding hidden merged cells in Microsoft Excel. For any additional information, or if you have further queries about what to do with merged cells once you’ve found them, check out our FAQs below.
FAQs
What are merged cells in Microsoft Excel?
Merged cells in Excel are two or more individual cells that have been combined to create one single cell. They are not always easy to locate and may exist in several different areas across your document, especially if your spreadsheet contains a large array of data. Merged cells are usually user-created, but can crop up by accident, too.
Why would I want to find merged cells in Microsoft Excel?
Merged cells in Microsoft Excel can be handy, or even unproblematic. However, if you were to try and conduct calculations using a cell grouping that contained merged cells, or wanted to ‘Sort’ the data contained within a group containing merged cells, you may quickly come up against an error message.
In other words, merged cells can act like hidden trip mines for an Excel user. Invisible to the untrained eye, they may mess up your calculations, organizational efforts, or stop you from conducting them in the first place. Finding merged cells in Microsoft Excel is the first step you must take if you’re looking to overcome error messages and correct these erroneously merged cells.
How do I unmerge merged cells in Excel?
Once you have located your merged cells in the given selection, using the Search/Find function detailed above, highlight all of the merged cells located (still in the Find menu) by clicking on the first merged cell, and using SHIFT+ARROW KEYS to highlight the entire selection. Then, with these highlighted, head to the Home tab at the top of the page, locate the ‘Merge & Center’ button, and click on it. All merged cells found within your selection will now be unmerged.
What to do with unmerged cells?
Once you’ve unmerged cells, you can delete those you no longer need. Likely, there will be some blank or empty cells, or even entire rows or columns, which were previously mucking up your calculations and sorting efforts. Delete these by highlighting them and pressing DELETE on your keyboard, or right-clicking on the row/column and selecting ‘Delete’ from the pop-up menu. Doing so should sort your problem allowing you to calculate and sort without a hitch.
