In addition to performing a basic find and replace operation in Excel 2010, you can use advanced options (in the expanded Find and Replace dialog box) to carry out a more detailed search operation. For example, you can choose to display all found items within the dialog box, and you also can perform a search based on the cell formatting.
Find all data occurrences
To display a list of all occurrences of the data you want to find, follow these steps:
Press Ctrl+F to open the Find and Replace dialog box.
In the Find What box, enter the data you want to locate.
Click Options and specify any desired options.
Click Find All.
The Find and Replace dialog box expands, showing a list of each cell entry that contains your data. You can sort the results by clicking a column heading. Also, you can click a cell in the list to jump to that cell.
Use Find All to display a list of all found results in the Find and Replace dialog box.
Locate cells based on formatting
Follow these steps to find worksheet data based on the formatting applied to that data:
Press Ctrl+F to open the Find and Replace dialog box.
Click Options.
Verify that these options are the ones you want to use.
Click Format.
The Find Format dialog box appears.
Select any formatting options for which you want to search.
You can choose any combination of options, including fill color.
You can find cells in Excel 2010 based on Format options.Click OK.
A preview of the formatting appears in the Preview box.
In the Find What box, enter the value or word you want to locate.
Leave this blank if you want to locate all cells with the specified formatting, regardless of the cell contents.
Click the Find or the Find All button.
You also can perform a find and replace operation that replaces the found data with the text and formatting you specify. To do so, specify Format options for both Find What and Replace What on the Replace tab of the expanded Find and Replace dialog box.