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.
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.
The following 3 characters have special meaning in the Find and Replace dialog box:
* to search for this character enter ~*
? To search for this character enter ~?
~ to search for this character enter ~~
Understanding the Options
These options are the same on both the Find and Replace tabs
Within - This can be either "Sheet" or "Workbook". If Sheet is chosen and a single cell is active, then the whole worksheet will be searched. If Sheet is chosen and a range of cells is selected then only that range is searched. The default is Sheet.
Search - This can be either "By Rows" or "By Columns". The default is By Rows.
Look in - This can be either "Formulas", "Values" or "Comments". The default is Formulas.
Match case - When you want the cell contents to distinguish between uppercase and lowercase characters.
Match entire cell contents - When you want the cell contents to be an exact match for your string.
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.
Remember if you are searching for values, you need to change the "Look in" box to Values.
Pressing "Find All" will display a list of all the cells that contain that value.
You can use the arrow keys to quickly move between all the matching cells.
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.
Searching for Formulas
For example you could use the following criteria to find all the cells that use the SUM function.
Searching for Comments
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
Type the text you want to search for, the location and the file types to search.
If only one cell is selected and you choose to search within the "Sheet" then the entire worksheet will be searched.
If a range of cells is selected and you choose to search within the "Sheet" then only that particular range of cells 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 cannot use this method to replace '=" with '=' ??
When using the Find & Replace be sure to check the "Match entire cell contents" 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).
© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext