Using Find and Replace
WHEN YOU WORK WITH large workbooks, you may often find yourself wishing you could locate a specific piece of data quickly. Using Find, you can. Find allows you to search through data, formulas, and comments in a worksheet to locate the exact piece of information you are searching for. Once something is found, you can optionally replace that something with something else. In a large workbook, this feature is quite useful, as you can make massive changes throughout the workbook to the name of a product, the reported value of an investment, or any other data.
Searching for Data
As mentioned earlier, Excel can search a worksheet’s data, formulas, or comments. When searching though formulas, Excel searches not only the formulas themselves, but also the data in the cells to which they refer. If you search through data or comments, Excel searches only through the actual data entered in cells, or the comments attached to cells. You might search a large worksheet for a particular employee, office, or product, and then make some changes to that item’s data.
Tip
You can search the current worksheet only, or all the worksheets in a workbook.
1. | If you want to search within a limited range, select that range of cells. Otherwise, click any cell in the worksheet/workbook you want to search.
|
2. | Click the Find & Select button on the Home tab.
|
3. | Select Find from the pop-up menu. The Find and Replace dialog box appears, as seen in Figure 6-23.
|
4. | Type the data to find in the Find What box.
|
5. | To set options, click the Options button and make your selections:
To search for data that uses a particular format, click the Format button, select the format to search for (such as Currency format), and click OK to return to the dialog box. You can select a cell that contains the formats to find by clicking the Choose Format From Cell button in the Find Format dialog box and clicking a cell. Normally, Excel searches only within the current worksheet. To search within the entire workbook, select Workbook from the Within list. In order to decrease the time in which it takes Excel to locate the data you’re searching for, you might want to indicate the direction in which you want Excel to search the sheet. Just open the Search list and select either By Rows or By Columns. Normally, Excel searches formulas and the cells to which they refer. To search data or comments only, open the Look In list and select Values or Comments. To match text by exact upper- and lowercase, select the Match Case checkbox. To find cells that contain only the contents you are searching for, select the Match Entire Cell Contents checkbox.
|
You can use wildcards when searching, such as * or ?. Use ? to represent a single unknown character, as in Sm?th to find Smith and Smyth, for example. Use * to represent any number of unknown characters, such as some*, which matches something, someday, and somebody. If you are looking for data that contains a ? or * you must type a tilde ~ in front of the character so Excel doesn’t treat it as a wildcard, as in ~? or ~*.
|
|
6. | Click either Find Next (to highlight the first cell that matches your criteria) or Find All (to display a list of all matching cells).
If you clicked Find Next, the cursor moves to the first matching cell. If you want, you can continue searching to the next matching cell by clicking Find Next again. Continue clicking Find Next to search for matching cells one at a time. If you clicked Find All, a list of matching cells appears at the bottom of the Find and Replace dialog box, as seen in Figure 6-24. You can drag a corner of the dialog box to make it wider and longer in order to make the list easier to use. Select a cell in the list to move the cursor to that cell.
|
7. | When you’re through searching, click Close.
|
Replacing Cell Data
Excel can find data in a worksheet, and can replace it with other data as well. When performing a search and replace, you can choose to replace individual instances of the matching data, or all matches. Again, you can search and replace data in the current worksheet or the entire workbook. Follow these steps:
1. | If you want to search within a limited range, select that range of cells. Otherwise, click any cell in the worksheet/workbook you want to search.
|
2. | Click the Find & Select button on the Home tab.
|
3. | Select Replace from the pop-up menu. The Find and Replace dialog box appears, as shown in Figure 6-25.
|
4. | Type the data to find in the Find What box.
|
5. | Type the data to use as the replacement in the Replace With box.
|
Wildcards such as * (to replace multiple characters) or ? (to replace a single character) can be used in searching. If you are looking for data that contains a ? or * you must type a tilde ~ in front of the character so Excel doesn’t treat it as a wildcard, as in ~? or ~*.
|
|
6. | To set options, click the Options button and make your selections:
To search for data that uses a particular format, click the Format button next to the Find What list, select the format to search for (such as Currency format), and click OK to return to the dialog box. You can select a cell that contains the formats to find by clicking the Choose Format From Cell button in the Find Format dialog box and clicking a cell. To replace data and apply a particular format, click the Format button next to the Replace With list, and follow the process described previously for selecting formats. Normally, Excel searches only within the current worksheet. To search within the entire workbook, select Workbook from the Within list. In order to decrease the time in which it takes Excel to locate the data you’re searching for, you might want to indicate the direction in which you want Excel to search the sheet. Just open the Search list and select either By Rows or By Columns. Normally, Excel searches formulas and the cells to which they refer. To search data or comments only, open the Look In list and select Values or Comments. To match text by exact upper- and lowercase, select the Match Case checkbox. To find cells that contain only the contents you are searching for, select the Match Entire Cell Contents checkbox.
|
7. | Perform one of the following:
Click Find Next to highlight the first cell that matches your criteria. The cursor moves to the first matching cell. If you want, you can continue searching to the next matching cell by clicking Find Next again. Click Find All (to display a list of all matching cells). A list of matching cells appears at the bottom of the Find and Replace dialog box. Drag a corner of the dialog box to make it wider and longer in order to make the list easier to use. Select a cell in the list to move the cursor to that cell. Once a cell is highlighted, click Replace to replace the matching data with the replacement data you entered in Step 5. To replace all matching cells without highlighting each one first, click Replace All.
|
8. | When you’re through searching and replacing, click Close.
|
