Article ID: 288291 - View products that this article applies to.
This article was previously published under Q288291
Expand all | Collapse all

On This Page

SUMMARY

This step-by-step article tells you how to use the Find and Replace dialog box in Microsoft Excel 2002 and in later versions of Excel. In Excel 2002 and in later versions of Excel, the separate Find and Replace dialog boxes are replaced by a single Find and Replace dialog box with new search and replace options. This article explains the new options.

Find and Replace in one dialog box

The Find and Replace dialog boxes of earlier versions of Excel are replaced by a single dialog box. The default tab selection depends on which of the two commands you use to open the dialog box. You can switch between the Find feature and the Replace feature as needed.

Options

Additional searching options are now available in the Find and Replace dialog box, including searching by formats, replacing formats, and searching the current workbook or worksheet. All of these options are hidden by default. These new features are displayed when you click the Options button.

Find by format

You can now search based on cell formatting. If you select a format, Excel searches only those cells that have the same format. For example, if you select bold, italics, and currency format with two decimal places, only cells that have all of those attributes will be searched for a match.

You can specify the format in the Find Format dialog box (click Format in the Find and Replace dialog box), which is exactly the same as the Format Cells dialog box.

To locate the Format Cells dialog box, use one of the following procedures, as appropriate for the version of Excel that you are running:
  • In Microsoft Office Excel 2007, click the Home tab, click Format in the Cells group, and then click Format Cells.
  • In Microsoft Office Excel 2003 and in earlier versions of Excel, click Cells on the Format menu
Alternatively, you can click Choose Format From Cell in the Format drop-down list, and then click any cell in any open workbook to specify that cell's format.

Replace with formatting

You can change the format in cells that match your search. For example, you can apply bold formatting to all cells with the SUM function. You can specify the format in the same way as you find (or search) by format, either by specifying the format in the Replace Format dialog box or by selecting a cell with the appropriate format.

Find Where

You can specify to search the entire workbook or the current worksheet. In earlier versions of Excel, your searching ability was limited to the current worksheet. If you have more than one worksheet selected, Excel now searches all selected worksheets in the workbook.

Find All and the Results list

When you click Find All, all matching cells are displayed in the Results list. Any item in the list is a hyperlink; therefore, by clicking any of the results on the results list, you activate that cell.

Modeless

You can format cells, run macros, and save and edit your workbook while the Find and Replace dialog box is open. You can use this feature with the Results list to edit cells that match your search. For example, you can search for # to fix any errors in your workbook.

REFERENCES

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
282151 Macro that uses FindNext to search for specific format finds wrong cell in Excel
282153 Error message when you use a macro to search for a fill color in Excel: "Run-time error '91'"
284881 "Replace All" command replaces cells in unselected worksheets in Excel 2002 or in Excel 2003

Properties

Article ID: 288291 - Last Review: April 19, 2012 - Revision: 6.0
APPLIES TO
  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Office Excel 2003
  • Microsoft Excel 2010
Keywords: 
kbexpertisebeginner kbhowtomaster kbpending KB288291

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com