Excel Find and Replace

Basic Excel Find

In order to find an occurrence of a specific value in an Excel spreadsheet:

Click on the Find & Select icon (which is located within the 'Editing' group on the Home tab of the Excel ribbon), then select the option Find... (see rightabove).
Note that the keyboard shortcut for this is Ctrl + F.
You will be presented with the Excel Find and Replace dialog box, with the Find tab selected, as shown below:
Within the dialog box:
- Type the text or numeric value that you want to find into the Find what: field;
- Click on the Find Next button.

This will take you to the next occurrence of the required value within the current worksheet.

Excel Find All

If you want to find all occurrences of a specific value, you can click on the Find All button within the Find and Replace dialog box. This brings up a list of all occurrences of your search value, as shown on the rightabove.

Clicking on each of the values in the list will take you to the corresponding cell in your spreadsheet.

Basic Excel Find and Replace

In order to replace one or more occurrences of a specific value in an Excel spreadsheet:

Click on the Find & Select button (which is located within the Editing group of the Home tab), then select the option Replace... (see rightabove).
Note that the keyboard shortcut for this is Ctrl + H.
You will be presented with the Excel Find and Replace dialog box, with the Replace tab selected, as shown below:
Within the dialog box:
- Type the text that you want to find into the Find what: field;
- Type the text that you want to replace with into the Replace with: field;
  Note that you can leave this field blank if you simply wish to remove all instances of the 'find text', (i.e. replace with nothing).
- Click on the Find Next button. This will take you to the first occurrence of the 'find text'.
- In order to replace the current instance of the 'find text' with the specified 'replace' text, click on the Replace button. The text will be replaced and you will be taken to the next occurrence of the 'find text'.

Excel Replace All

If you are confident that you want to replace all occurrences of the 'find text' with the 'replace text' (without checking each instance individually), simply click on the Replace All button within the dialog box.

Using Wildcards Within the Excel Find Command

The Excel Find command can handle the following wildcards:

? - matches any single character
* - matches any sequence of characters

(Note that, if you actually want to find the ? or * character, you need to type the ~ symbol before this character in your search).

For example:

The search term condition "a*e" will find all cells containing the letter "a" followed by an "e", with any combination of characters falling between the "a" and the "e".
The search term condition "a?e" will find all cells containing the letter "a" followed by any single character and then the letter "e".

Find Within a Specified Range of Cells

If you want the Excel find command to only search a specific range of cells, select the range that you want to search before performing the find (or the find and replace).

For example, in the above spreadsheet on the right, the range A2 - A6 is selected. Therefore, a search for the text string "Smith" will find this text string in cells A2 and A5, but will not find the text string "Smith" that occurs in cell A8.

Additional Find Options

The Excel Find command can be refined using a number of options, which can be displayed by clicking on the Options >> button in the Find and Replace dialog box.

Clicking on the Options >> button causes the dialog box to expand as shown below:

Note that most of these options are also available in the Replace tab of the dialog box.

Each of the options is discussed below:

Within: Option

The Excel Find Within: Option allows the user to decide whether the Find command should search in the current active worksheet only, or throughout the entire current workbook.

Search: Option

The Excel Find Search: Option allows the user to decide the order that Excel searches through a worksheet.

If this option is set to 'By Rows', Excel searches across each row before continuing the search at the start of the next row, etc.
If this option is set to 'By Columns', Excel searches down each column before continuing the search at the top of the next column, etc.

Look in: Option

The Look in: Option allows the user to decide what is to be searched. Possible options are:

Formulas - If a cell contains a formula, the text that makes up the formula will be searched, rather than the result of the formula.
Values (not available on the Replace tab) - If a cell contains a formula, the result of the formula will be searched (not the actual formula itself).
Comments (not available on the Replace tab) - Only cell comments are searched; Any other cell contents are ignored.

Match case Option

The Match case option allows a user to request that the Find command should be case-sensitive.

If the Match case option is not checked (the default option), the Excel Find command is not case-sensitive.
If the Match case option is checked, the Excel Find command is case-sensitive.

Match Entire Cell Contents Option

The Match Entire Cell Contents option allows a user to request whether the Find command should match any part of a cell's contents or should only match if the 'find text' is equal to an entire cell's contents.

If the Match Entire Cell Contents option is not checked (the default option), the Excel Find command finds any instances of the 'find text' that occur in any part of a cell.
If the Match Entire Cell Contents option is checked, the Excel Find command only finds cells for which the entire contents of the cell match the 'find text'.

Find and Replace a Formatting Style

Also within the Excel Find and Replace dialog box, you will see the Format... button. This allows you to specify a format that you want to find and (optionally) a Replace format.

Note that if you specify a formatting style and a 'find text' value, the Find command will only find cells that match both the formatting style and the specified 'find text'.

How to Remove a Formatting Style in Excel Find and Replace

If you want to remove a previously specified formatting style from the Find and Replace dialog box, click on the arrow at the side of the Format... button and select the option Clear Find Format (see below).

Option to Clear a Format in the Find and Replace Dialog Box

Further details on the Excel Find and Replace command are provided on the Microsoft Office Support website.

Return to the Basic Excel Page
Return to the ExcelFunctions.net Home Page