Lost in Yonkers? Using Excel’s Find Options
The Find and Replace functions in Word perform a lot of heavy lifting to make repurposing documents easier, quicker and more accurate. When it comes to Excel, Find and Replace become powerful tools for troubleshooting worksheets. Can’t find merged cells? Need to locate unlocked cells? Want to find cells with similar formatting.
In the following example, when using AutoSum for the numbers in column C, only the range C6:C8 is selected. Something is off with cell C5 and it appears to be merged cells. Extrapolate this issue to a worksheet with hundreds of columns and thousands of rows and it becomes a laborious task locating multiple merged cells.
To locate the merged cells in any worksheet, from Home | Editing, click Find & Select and select Find…. Click the Options button to expand dialog box. With the insertion point in the Find What box, click the Format button. Excel displays the Find Format dialog box. Click the Alignment tab and check Merge Cells. Click OK to close. Click Find All. A list of the worksheet’s merged cells display at the bottom of the dialog box. Click the Find Next button to move to each merged cell to unmerged it so that the columns will total correctly.
Using the same technique, you can locate unlocked cells in a worksheet as well as different types of numbering formatting. Any attribute on the Format cells dialog box can be quickly located using Find and Replace. Although this functionality is not hidden, it is often overlooked. One final tip, click the Format options arrow, select Choose Format From Cell, select a specific cell to capture its formatting then perform a search for similarly formatted cells.
No more excuses for wandering around a worksheet with Excel’s Find command as your guide.