Finding information in your Excel spreadsheets is greatly simplified with the search features included in modern versions of Excel. Such options include case-sensitive searching, formula searching and searching for formatted text, among others.
Video of the Day
On the Home ribbon, click "Find & Select" to open a drop-down menu of search options. Click "Find" to open the Find and Replace dialog. Type the text you're searching for in the "Find" box. Click "Find All" to generate a list of all instances of that text in the currently selected spreadsheet, or click "Find Next" to search down through the current spreadsheet for the next instance of that term.
To find text in your spreadsheet that you want to replace with predefined information (for example, changing all "&" symbols to "and"), click "Replace" from the "Find & Select" menu. Enter the text you're searching for in the "Find" box and the text you want to replace it with in the "Replace with" box. Click "Replace All" to replace all instances of the text automatically, or click "Find Next" and then "Replace" to find the first instance of the text. After finding the first instance, Excel finds the next instance automatically, so you only have to click "Replace." To skip replacing any instance of the text, just click "Find Next."
Find and Replace Options
On either the "Find" or "Replace" tabs, click the "Options" button. You can further refine your find or replace function by searching only for text that is formatted a certain way, and choose to replace the text with custom formatted text as well. To find or replace terms in any spreadsheet in the current workbook, click the "Options" button in the Find and Replace dialog, then select "Workbook" from the Within drop-down menu. The "Options" button also gives you access to the ability to search your spreadsheet or workbook row-by-row or column-by-column, or to search within formulas, cell values or comments. Finally, you can also make your search case-sensitive by clicking the "Match case" check box, or look for cells containing only your search term by clicking the "Match entire cell contents" check box.
Go To and Select
If you have defined cell ranges by name in your workbook, click the "Go To" option from the "Find & Select" menu to choose a range and go directly to it. "Go To Special" allows you to select cells containing certain information in the current spreadsheet, including formulas, constant values, precedent and dependent cells, blank cells and comments. The "Find & Select" drop-down menu also lists several of these options for faster access.