Skip to content

Lost in Yonkers? Using Excel’s Find Options

July 30, 2012

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.

(Sample worksheet)

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.

(Find and Replace dialog box)

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.

Advertisements
2 Comments leave one →
  1. Liz Wilson permalink
    July 30, 2012 6:59 pm

    I thought I knew every trick! Thanks

    • July 30, 2012 7:21 pm

      Thanks Liz. This tip always comes in handy when working with a worksheet that I did not create.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: