Section 5.2. Find and Replace | Excel 2007 for Starters: The Missing Manual

5.2. Find and Replace

When you're dealing with great mounds of data, it's tough to ferret out the nuggets of data you need. Fortunately, Excel's find feature is great for helping you locate numbers or text, even when they're buried within massive workbooks holding dozens of complex worksheets. And if you need to make changes to a bunch of identical items, you'll discover the find-and-replace option to be a real time-saver.

The find feature includes both simple and advanced options. In its basic version, you're only a quick keystroke combo away from a word or number you know is lurking somewhere in your data pile. With the advanced options turned on, you can do things like search for cells that have certain formatting characteristics and then apply changes automatically. The next few sections dissect these features.

5.2.1. The Basic Find

Excel's find feature is a little like the Go To tool described in Chapter 1, which lets you leap across a large expanse of cells in a single bound. The difference is that Go To moves to a known location, using the cell address you specify. The find feature, on the other hand, searches every cell until it finds the content you've asked Excel to look for. If you're familiar with the search feature in Microsoft Word, you'll be happy to know that Excel's search works pretty much the same way, with these few Excel-specific additions:

Excel searches by comparing the content you type in the "Find and Replace" dialog box with the content in each cell. For example, if you search for the word Date, Excel identifies as a match a cell containing the phrase Date Purchased.
When searching cells that contain numeric or date information, Excel always searches the display text. (For more information about the difference between the way Excel displays a numeric value and the underlying value Excel actually stores, see the box on Section 4.1.1.7.)
For example, say a cell displays dates using the day-month-year format, like 2-Dec-05. You can find this particular cell by searching for any part of the displayed date (using search strings like Dec or 2-Dec-05). If you use the search string 12/2/2005, you won't find a match because the search string and the display text are different. A similar behavior occurs with numbers. For example, the search strings $3 and 3.00 will match the currency value $3.00. However, the search string 3.000 won't turn up anything, because Excel won't be able to make a full text match.
Excel searches one cell at a time, from left to right. When it reaches the end of a row, it moves to the first column of the next row.

To perform a find operation, follow these steps:

Move to the cell where you want the search to begin.
If you start off halfway down the worksheet, for example, the search covers the cells from there to the end of the worksheet and then "loops over" and starts at cell A1. If you select a group of cells, Excel restricts the search to just those cells. You can search across a set of columns, rows, or even a non-contiguous group of cells simply by selecting the columns, rows, or groups you want to search.
Choose Edit Find, or press Ctrl+F.
Note: To help out with frequent searches, Excel lets you keep the "Find and Replace" window hanging around (rather than forcing you to use it or close it, as is the case with many other dialog boxes). This means you can continue to move from cell to cell and edit your worksheet data even while the "Find and Replace" window remains visible.
In the "Find what" combo box, type in the word, phrase, or number you're looking for.
If you've performed other searches recently, you can reuse these search terms. Just choose the appropriate search text from the "Find what" drop-down list.
Click Find Next.
Excel jumps to the next matching cell, which becomes the active cell. However, Excel won't highlight the matched text or in any way indicate why it decided the cell was a match. If it doesn't find a matching cell, Excel displays a message box telling you it couldn't find the requested content.
If the first match isn't what you're looking for, you can keep looking by clicking Find Next again to move to the next match. Keep clicking Find Next to move through the worksheet. When you reach the end, Excel resumes the search at the beginning of your worksheet, potentially bringing you back to a match you've already seen. When you're finished with the search, click Close to dismiss the "Find and Replace" window.

GEM IN THE ROUGH
The Find All Feature

One of the problems with searching in Excel is that you're never quite sure how many matches there are in a worksheet. Find Next gets you from one cell to the next, but with Find All, Excel searches the entire worksheet in one go, and compiles a list of matches. In the example shown here, the search for "Price" matched three cells in the worksheet.

The Find All button doesn't lead you through the worksheet like the find feature. You select one of the results in the list, and then Excel automatically moves you to the matching cell.

The Find All list won't automatically refresh itself: after you've run a Find All search, if you add new data to your worksheet, you need to run a new search to find any newly added terms.

However, Excel does keep the text and numbers in your found-items list synchronized with any changes you make in the worksheet. For example, if you change cell D5 from "Total Prices" to "Total Price," the change appears in the Value column in the found-items list automatically. As you can guess, Find All is a great tool for editing a worksheet, because you can keep track of multiple changes at a single glance.

Finally, the Find All feature is the heart of another great Excel guru trick: it gives you another way to change multiple cells at once. After you've performed the Find All search, select all the entries you want to change from the list by clicking them while you hold down Ctrl (Ctrl-clicking lets you select several at once). Then, click in the Formula Bar and start typing the new value. When you're finished, hit Ctrl+Enter to apply your changes to every selected cell. Voilàit's like Find and Replace, but you're in control!

5.2.2. More Advanced Searches

Basic searches are fine if all you need to find is a glaringly unique phrase or number (Pet Snail Names or 10,987,654,321). But Excel's advanced search feature gives you lots of ways to fine-tune your searches or even search more than one worksheet. To conduct an advanced search, open the "Find and Replace" window (Edit Find) and click the Options button, as shown in Figure 5-9.

Figure 5-9. Top: Clicking the Options button in the standard "Find and Replace" window displays the expanded window shown below.
Bottom: The expanded "Find and Replace" window gives you a slew of additional settings that allow you to configure things like search direction (rows vs. columns), case sensitivity, and format matching.

You can set any or all of the following options:

The standard Within option, Sheet, tells Excel to examine all the cells in the currently active worksheet. If you want to continue the search in the other worksheets in your workbook, head to the Within box and choose Workbook. Excel examines the worksheets from left to right. When it finishes searching the last worksheet in your workbook, it loops back and starts examining the first worksheet.
The Search pop-up menu lets you choose the direction you want to search. The standard option, By Rows, completely searches each row before moving on to the next one. That means that if you start in cell B2, Excel searches C2, D2, E2, and so on. Once it's moved through every column in the second row, it moves onto the third row and searches from left to right.
On the other hand, if you choose By Columns, Excel searches all the rows in the current column before moving to the next column. That means that if you start in cell B2, Excel searches B3, B4, and so on, until it reaches the bottom of the column, and then it starts at the top of the next column (column C).

Note: The search direction determines which path Excel follows when it's searching. But whichever direction you choose, the search will still ultimately hit every cell in your worksheet (or the current selection).

The "Match case" option lets you specify whether capitalization is important. If you select "Match case," Excel finds only words or phrases whose capitalization matches. So, for example, searching for Date matches the cell value Date but not date.
The "Match entire cell contents" option lets you restrict your searches to the entire contents of a cell. Excel ordinarily looks to see if your search term is contained anywhere inside a cell. So if you specify the word Price, Excel finds cells containing text like Current Price and even Repriced Items (assuming you didn't turn on the "Match case" option described previously). Similarly, numbers like 32 match cell values like 3253, 10032, and 1.321. Turning on the "Match entire cell contents" option forces Excel to be precise. In this case, a search for Price matches a cell only if that cell contains Price (and nothing but Price).

Note: Remember, Excel searches for numbers as they're displayed (as opposed to looking at the underlying values that Excel uses to store numbers internally). That means that if you're searching for a number formatted using the dollar Currency format ($32.00, for example) and you've turned on the "Match entire cell contents" checkbox, you'll need to type in the number exactly as it appears on the worksheet. So, for example, typing in $32.00 may yield a match, but 32 alone won't.

POWER USERS' CLINIC
Searching for the Unknown

Sometimes you sorta, kinda know what you're looking forfor example, a cell with some version of the word "date" in it (as in "date" or "dated" or "dating"). What you really need is a search tool that's flexible enough to keep its eyes open for results that are similar but not exactly alike. Power searchers will be happy to know that Excel lets you use wildcards in your searches. Wildcards are search symbols that let you search for variations on a word.

The asterisk (*) wildcard represents a group of one or more characters. For example, a search for s*nd finds any word that begins with the letter s and ends with the letters nd; for example, it would find words like sand, sound, send, or even the bizarre series of characters sgrthdnd. The question mark ? wildcard represents any single character. For example, a search for f?nd turns up find or fund but not friend.

Wildcards are particularly useful when you're using the "Match entire cell contents" option. For example, if you turn on the "Match entire cell contents" option and type in the search term date* you'll find any cell that starts with the word date. In contrast, if you performed the same search without turning the "Match entire cell contents" option on, you'd find any cell containing the word date.

If you happen to want to search for special characters like the asterisk or the question mark, you'll need to use the tilde (~) before the wildcard. For example, the search string ~* searches for cells that contain the asterisk symbol.

5.2.3. Finding Formatted Cells

Excel's find and replace is an equal opportunity search tool: it doesn't care what the contents of a cell look like. But what if you know, for example, that the data you're looking for is formatted in bold, or that it's a number that uses the Currency format? You can use these formatting details to help Excel find the data you want and ignore cells that aren't relevant.

To use formatting details as part of your search criteria, follow these steps:

Launch the Find tool.
Choose Edit Find, or press Ctrl+F. Make sure that the Find and Replace window is showing the advanced options shown in Figure 5-9. (If its not, click the Options button to display the advanced options.)
Click the Format button.
The Find Format dialog box appears (Figure 5-10), which contains the same options as the Format Cell dialog box covered in Chapter 4.
Specify the format settings you want to look for.
Using the Find Format dialog box, you can specify any combination of number format, alignment, font, fill pattern, and bordersany, that is, except those formatting settings that appear blank or grayed. (Chapter 4 explains formatting settings in detail.)
Note: In some versions of Windows, the checkboxes you can't use to specify search criteria appear to be filled with a solid square. In other versions of Windows, they appear dimmed and checked at the same time (as with the "Merge cells" setting in Figure 5-10). Bottom line: if checkboxes are filled with a solid square or dimmed and checked, Excel won't use these settings as part of its search.
When you're finished, click OK to return to the Find and Replace window.
Next to the "Find what" search box, a preview appears, indicating the formatting of the cell that you'll be searching for, as shown in Figure 5-11.

Figure 5-10. This is the Alignment tab in the Find Format dialog box. You can tell Excel to serach based on any formatting options, except those that appear blank or grayed. In this example, Excel won't search based on Text alignment, and it won't search for merged cells or cells for which you've opted to shrink to fit cell boundaries.

If you change your mind or get confused trying to remember which of the three zillion settings you specified, no problem: just start over. To remove your formatting restrictions, click the pop-up menu to the right of the Format button and then choose Clear Find.

Note: Rather than specifying all the format settings manually, you can copy them from another cell. Just click the Choose Format From Cell button at the bottom of the Find Format dialog box. The pointer will change to a plus symbol with an eye dropper next to it. Next, click any cell that has the formatting you want to match. Keep in mind that when you use this approach, you copy all the format settings.

Figure 5-11. The Find Format dialog box shows a basic preview of your formatting choices. In this example, the search will find cells containing the word "price" that also use white lettering, a black background, and the Bauhaus font.

5.2.4. Finding and Replacing Values

You can use Excel's search muscles not only to find the information you're interested in, but also to modify cells quickly and easily. Excel lets you make two types of changes using its replace tool:

You can automatically change cell content. For example, you can replace the word Colour with Color or the number $400 with $40.
You can automatically change cell formatting. For example, you can search for every cell that contains the word Price or the number $400 and change the fill color. Or, you can search for every cell that uses a specific font and modify these cells so they use a new font.

Here's how to perform a replace operation. The box on Section 5.3 gives some super-handy tricks you can do with this process:

Move to the cell where you want the search to begin.
Remember, if you don't want to search the entire spreadsheet, just select the range of cells you want to search.
Choose Edit Replace, or press Ctrl+H.
Figure 5-12. The Replace tab looks pretty similar to the Find tab. Even the advanced options are the same. The only difference is that you also need to specify the text you want to use as a replacement for the search terms you find.
In the "Find what" box, type in your search term. In the "Replace with" box, type in your replacement text.
Type the replacement text exactly as you want it to appear. If you want to set any advanced options, click the Options button (see the earlier sections "More Advanced Searches" and "Finding Formatted Cells" for more on your choices).
Perform the search.
You've got four different options here. Replace All immediately changes all the matches your search identifies. Replace changes only the first matched item (you can then click Replace again to move on to subsequent matches, or you can select any of the other three options). Find All works just like the same feature described in the box on Section 5.2.2: it displays all the matching cells and gives you the opportunity to replace one or more (or all) of them by selecting the match and clicking Replace. Find Next moves to the next match, where you can click Replace to apply your specified change, or click any of the other three buttons. The replace options are good if you're confident you want to make a change (and confident you've identified the change correctly); the find options work well if you first want to see what changes you're about to make. Of course, nothing is etched in stone: you can reverse either option using the normal Edit Undo command.

POWER USERS' CLINIC
Mastering the Art of Replacement

You can use the find-and-replace feature in many imaginative ways. Here are just a few examples:

You can automatically delete a specific piece of text. Just type in the appropriate "Find what" text, and leave the "Replace with" box blank.

You can change the formatting used in specific cells. Just type the same text in both the "Find what" and "Replace with" text, and then click the Format button next to the "Replace with" combo box to set some formatting attributes. (You don't need to specify any formatting settings for your "Find what" search criteria.)

You can change the formatting in a bunch of cells, all at once. For example, imagine you have a worksheet that has several cells bolded. Say you want to adjust the formatting of these cells to use a new font. To perform this operation, leave both the "Find what" and "Replace with" boxes blank. Then, set the formatting search criteria to look for the bold font attribute and set the replacement formatting to use the new font. Click Replace All, and all the cells that currently have bold formatting will acquire the new font. This approach is tricky to master, but it's one of the most powerful formatting tricks around.