You can easily locate all of your merged cells in Excel on Windows by using the Find tool. Or, you can unmerge all of your cells in Excel on Mac.
Have you ever tried to sort data in Excel, only to receive an error about merged cells? It can be a pain to try and locate all of those merged cells manually. Luckily, there’s a simple way to find them.
Find Merged Cells in Excel on Windows
Microsoft Excel on Windows offers a Find tool that offers extra options. This allows you to locate cells with specific formatting.
Note: Currently, Excel on Mac doesn’t have this robust search tool. These instructions currently only apply to Windows. On Mac, you’ll need to unmerge all of your cells (as discussed below).
RELATED: How to Find and Replace Text and Numbers in Excel
Head to the sheet where you want to find the merged cells and go to the Home tab. Select the “Find & Select” drop-down arrow and pick “Find.”
When the Find and Replace box opens, click “Options” on the bottom right.
After you see these additional options, you’ll also see a Format button to the right of the Find What box. Select the drop-down arrow for that Format button and pick “Format.”
In the Find Format box, go to the Alignment tab and check the box for Merge Cells. Click “OK.”
This brings you back to the Find and Replace window. You can leave the Find What box empty. Then, be sure that “Sheet” is selected in the Within box. You can keep the other options as-is.
Select “Find All” to see all of the sheet’s merged cells in the expanded portion of the window.
You can click one of the results to go directly to it in your sheet to unmerge the cell using the Merge and Center drop-down menu on the Home tab.
RELATED: How to Merge and Unmerge Cells in Microsoft Excel
As mentioned, the Find tool in Excel on Mac doesn’t offer the Format option like on Windows. But if you’re receiving the merged cell error when trying to sort data, you can simply unmerge all the cells in your sheet.
This isn’t ideal and may cause issues with the appearance or function of those merged cells. However, it’s still an option if you’re interested.
Select the entire sheet using the triangle button on the top left where column A and row 1 meet. Then, go to the Home tab, click the Merge and Center drop-down arrow, and pick “Unmerge Cells.”
When you have a spreadsheet containing a large amount of data, locating cells you’ve merged over time can be tedious. With the Find tool in Excel on Windows, you can locate the cells you need quickly.
For more, learn how to find circular references or how to list and sort unique values in Excel.
Source by www.howtogeek.com