|Excel Find and Replace Index:|
|Basic Excel Find|
|Basic Excel Find and Replace|
|Using Wildcards Within Excel Find|
|Find Within a Specified Range|
|Additional Find Options|
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:
This will take you to the next occurrence of the required value within the current worksheet.
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.
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 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).
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.
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).
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.
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.
The Excel Find Search: Option allows the user to decide the order that Excel searches through a worksheet.
The Look in: Option allows the user to decide what is to be searched. Possible options are:
The Match case option allows a user to request that the Find command should be case-sensitive.
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.
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.
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).
Further details on the Excel Find and Replace command are provided on the Microsoft Office Support website.