Your worksheets don’t have to get very big before you start wishing for a better way to find things than scrolling around and hunting for them. Read on to learn about Excel’s methods for taking you directly to the information you seek

In the Home tab’s

Basic Find Jobs

Let’s start simply: looking for one piece of information such as the sales quota for Steve Dinero in a large worksheet. Click Find & Select and Find to open the Find And Replace dialog box. Type Steve Dinero into the Find What box and click Find All. This automatically selects the first cell Excel finds containing that text and lists all cells containing the text. To move the selected cell to the next one containing the text, click Find Next. To back up, hold down the SHIFT key while clicking Find Next.

Find Options

Searches get more powerful when you click Options in the dialog box. The Format button lets you search for only data that matches your target string and the selected formatting. Let’s say you want to find all products priced at $1,999 in the spreadsheet. If you select Currency formatting, Excel skips over the year 1999, for example. You also can search by Format alone, telling Excel, for example, to locate all cells formatted as Currency. After a search, open the Find Format dialog box and click Clear. Otherwise, the format search remains in effect and could confuse your next search. Next, you can expand the search from the default setting of the worksheet you’re currently in to searching through the entire workbook.

The Search drop-down list handles searches where you specifically want Excel to look for the next occurrence of the target in the current row or the current column. The Look In option lets you search through values displayed in cells, formulas behind the cells, or comments attached to cells. These are seldom-used options.

Send In Replacements

The Replace tab of the Find And Replace dialog box shares most of the Find tab’s options. But, as you might guess, it includes a Replace option, which is pretty self-explanatory. Excel looks for whatever you put in the Find What box and swaps it for what you put in the Replace With box.

Remember a couple of tips, though. Click Replace to replace only the next occurrence of the info in the Find What box. Click Replace All to change every occurrence in the worksheet or workbook. To delete all occurrences of a certain string, enter it in the Find What box, leave the Replace With box blank, and click Replace All.

Find Other Types Of Content

The Find & Select button includes a few tools for jumping to specific kinds of content. The ones you’re most likely to use involve comments, formulas, and conditional formatting.

Excel adds a little triangle flag to cells containing comments, but for an overview of all cells holding comments, click Comments under Find & Select. Similarly, the Formulas choice helps you spot which cells are data and which contain formulas.

Conditional formatting changes a cell’s look, depending on the info it contains. You may, for example, set up negative figures to appear in red. Finding these cells can be hard as you can’t see the formatting unless the data meets your conditions. To find them, click Conditional Formatting.

Home Excel 2007 Find How To Find Almost Anything
Search MS Office A-Z   |   Search Web Pages/ Design A-Z