Leading the way in Microsoft Office Development

Step 1 - Using the (Edit > Find) dialog box

You can find values that match specific criteria by using the (Edit > Find) dialog box.

Alternatively you can use the shortcut key (Ctrl + F).

Cells can contain numbers, words, text, formulas, functions, formats and comments and this lets you search for any of those.

As formulas are not normally displayed in a cell being able to search within formulas is very useful.

When you perform a search Excel will find the first cell (after the active cell) that matches.

Find Next - This starts the search and will find the first cell matching your criteria after the active cell.. This button can be used again to find another occurrence.

Find All - This will highlight all the cells.

Step 2 - Using Wildcard Characters

You can include any letters, numbers, punctuation marks etc in your search criteria.

You can even include two wildcard characters when you do not know the exact value or spelling.

asterisk (*) - Denotes any number of missing characters.

question mark (?) - Denotes a single missing character.

Step 3 - Understanding the Options

These options are the same on both the Find and Replace tabs

Within - This can be either "Sheet" or "Workbook"

Search - This can be either "ByRows" or "ByColumns"

Look in - This can be either "Formulas", "Values" or "Comments"

Match case -

Match entire cell contents - When you want the cell contents to be an exact match for your string.

Step 4 - Searching for Numbers

For example you could use the following criteria to find all the cells that contain three digit numbers that start with a 1 and end with a 4.

Step 5 - Searching for Text

For example you could use the following criteria to find all the cells that contain the word "better".

Unless you tick the "Match entire cell contents" checkbox an asterisk will automatically be placed at the start and end of your search criteria.

The following two searches are equivalent.

Step 6 - Searching for Formulas

For example you could use the following criteria to find all the cells that use the SUM function.

Step 7 - Searching Another Workbook

It is possible to find workbooks (and other files) that contain specific text by using the Windows File Search method.

This can be accessed from the regular (File > Open) dialog box.

Click the Tools menu in the upper right corner and click Search

SS

Type the text you want to search for, the location and the file types to search.

Step 8 - Things to Remember

If only one cell is selected before the dialog box is displayed then the entire worksheet will be searched.

It is always a good idea to try and search a smaller area as possible as this will reduce the time taken.

You can restrict a search to a specific range of cells by highlighting the cells before displaying this dialog box.

When using the Find & Replace be sure to check the "Find Entire Cells Only" checkbox to find and replace exact matches.

You can press (Shift + F4) to search for the word or phrase last entered into a workbook, instead of pressing (Edit > Find Next).