Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.
- Create BookmarkCreate Bookmark
- Create Note or TagCreate Note or Tag
- DownloadDownload
- PrintPrint
4. Managing Worksheets > Find and Replace
When you're dealing with great mounds of information, you may have a tough time ferreting 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 worksheets. And if you need to make changes to a bunch of identical items, the find-and-replace option can be a real timesaver.
The Find and Replace 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 apply changes automatically. The next few sections dissect these features.
Excel's Find feature is a little like the Go To tool described in Chapter 1, which lets you move 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. Excel's search works similarly to the search feature in Microsoft Word, but it's worth keeping in mind a few additional details:
-
Excel searches by comparing the content you enter with the content in each cell. For example, if you searched 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 cell content, not 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 Number.)
For example, say a cell displays dates using the day-month-year format, like 2-Dec-10. Internally, Excel stores the date as 12/2/2010, which you'll see if you move to the cell and look in the formula bar. Thus, if you perform a search for 2010 or 12/2 you'll find the cell, because your search text matches part of the stored content. But if you search for Dec or 2-Dec-10, you won't find a match. A similar behavior happens with numbers. For example, the search string $3 won't match the currency value $3.00, because the dollar sign isn't part of the stored cell value—it's just a formatting detail. You can change this behavior and start searching the cell display text using the "Look in" setting described on More Advanced Searches.
-
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 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 noncontiguous group of cells.
-
Choose Home?Editing?Find & Select?Find, or press Ctrl+F.
The Find and Replace window appears, with the Find tab selected.
Note
To assist 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). 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, enter 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 doesn't highlight the matched text or in any way indicate why it decided the cell was a match. (That's a bummer if you've got, say, 200 words crammed into a cell.) 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 get rid of the Find and Replace window.
One of the problems with searching in Excel is that you're never quite sure how many matches there are in a worksheet. Sure, clicking Find Next gets you from one cell to the next, but wouldn't it be easier for Excel to let you know right away how many matches it found?
Enter the Find All feature. With Find All, Excel searches the entire worksheet in one go, and compiles a list of matches, as shown in Figure 4-11.
The Find All button doesn't lead you through the worksheet like the Find feature. It's up to you to select one of the results in the list, at which point Excel automatically moves you to the matching cell.
Figure 4-11. In the example shown here, the search for "Price" matched three cells in the worksheet. The list shows you the complete text in the matching cell and the cell reference (for example, $C$1, which is a reference to cell C1).
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 to Total Price, the change appears in the Value column in the found-items list automatically. This tool is great 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 (this trick lets you select several at once). Click the formula bar, and then 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!
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, begin by clicking the Options button in the Find and Replace window, as shown in Figure 4-12.
Figure 4-12. In the standard Find and Replace window (top), when you click Options, Excel gives you a slew of additional settings (bottom) so you can configure things like search direction, case sensitivity, and format matching.
You can set any or all of the following options:
-
The Within box controls the span of your search. The standard option, Sheet, searches all the cells in the currently active worksheet. If you want to continue the search in the other worksheets in your workbook, choose Workbook. When performing a workbook search, Excel examines your worksheets from left to right, starting with the current worksheet. When it finishes searching the last worksheet in your workbook, it loops back and starts again at the first worksheet in the workbook.
-
The Search box chooses the direction of the 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 starts at the top of the next column (column C).
-
The "Look in" box tells Excel what to examine in each cell. If you choose Formulas (the standard option), Excel tries to make a match between your search text and the cell content (for example, the number 3.5 or the date 12/2/2010). If you choose Values, Excel tries to make a match between your search text and the cell display text (for example, the formatted number $3.50 or the formatted date 2-Dec-10). And if you choose Comments, Excel searches the text of any attached comment boxes (Uploading to the Web), but ignores the actual cell content.
-
The "Match case" option specifies whether capitalization is important. If you select "Match case", Excel finds only words or phrases whose capitalization matches. Thus, 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. Similarly, a number like 32 will match cell values like 3253, 10032, and 1.321. Turning on the "Match entire cell contents" option forces Excel to be precise.
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 enter the number exactly as it appears on the worksheet. Thus, $32.00 would work, but 32 alone won't help you.
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 Home?Editing?Find & Select?Find, or press Ctrl+F. Make sure that the Find and Replace window is showing the advanced options (by clicking the Options button).
-
Decide how you want to specify the formatting.
You have two options, and they both involve the Format button that appears next to the "Find what" search box.
The quickest approach is to copy all the format information from another cell. To do this, click the arrow on the right-side of the Format button to pop open a menu with additional options, and then click Choose Format From Cell. The mouse pointer changes to a plus symbol with an eyedropper 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.
A more controlled approach is to specify the exact formatting settings you want to hunt down. To do this, click the Format button. The Find Format dialog box appears (Figure 4-13). It contains the same options as the Format Cells dialog box discussed on Changing the Cell Value Format. Using the Find Format dialog box, you can specify any combination of settings for number format, alignment, font, fill pattern, and borders. Chapter 5 explains all these settings in detail. You can also search for protected and locked cells, which are described in Chapter 24. When you're finished, click OK to return to the Find and Replace window.
-
Review your formatting and start your search.
Next to the "Find what" search box, a preview appears indicating the formatting of the cells that you're searching for, as shown in Figure 4-14. If everything checks out, click Find All or Find Next to get started.
To remove these formatting restrictions in subsequent searches, click the arrow on the right of the Format button, and then choose Clear Find Format.
Figure 4-13. In the Find Format dialog box, Excel won't use any formatting option that's blank or grayed out as part of its search criteria. For example, here, Excel won't search based on alignment. Checkboxes are a little trickier. In some versions of Windows, it looks like the checkbox is filled with a solid square. In other versions of Windows, it looks like the checkbox is dimmed and checked at the same time (as with the "Merge cells" setting in this example). Either way, this visual cue indicates that Excel won't use the setting as part of its search.
You can use Excel's search muscles to find not only 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. Once you've mastered the technique, check out the box on Mastering the Art of Replacement, which describes some super handy tricks you can do with this process.
-
Move to the cell where the search should begin.
Remember, if you don't want to search the entire spreadsheet, just select the range of cells you want to search.
-
Choose Home?Editing?Find & Select?Replace, or press Ctrl+H.
The Find and Replace window appears, with the Replace tab selected, as shown in Figure 4-15.
-
In the "Find what" box, enter your search term. In the "Replace with" box, enter the 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 to select any of the other three options). Find All works just like the same feature described on Find All. 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; the find options work well if you first want to see what changes you're about to make (although you can reverse either option using Ctrl+Z to fire off the Undo command).
