In this tutorial, you'll learn how to use Excel's Find and Replace feature to find specific data in a worksheet or workbook, and what you can do with those cells once you find them. We'll also explore advanced search features in Excel, such as wildcards, finding cells with specific formulas or formatting, finding and replacing across all open workbooks, and more.
When working with large spreadsheets in Excel, it is very important to be able to quickly find the information you need at any time. Scrolling through hundreds of rows and columns is definitely not a good idea, so let's take a closer look at what Excel's Find and Replace feature has to offer.
How to use Find in Excel
Below you will find an overview of Excel's search capabilities, as well as detailed instructions on how to use this feature in Microsoft Excel 365, 2021, 2019, 2016, 2013, 2010, and earlier versions.
Find a value in a range, worksheet, or workbook
The following tips provide guidance on how to find specific characters, text, numbers, or dates in a range of cells, a worksheet, or an entire workbook.
- First, select a range of cells to search. To search the entire worksheet, click any cell in the active worksheet.
- open excelfind and replacedialog by pressingControl + Fabbreviation. or go todom> tabversiongroup and clickfind and select>Come across…
- wfind thistype the search characters (text or number) and click one of themfind everythinglubricantfind the next one.
when you clickfind the next one, Excel selects the first occurrence of the lookup value in the worksheet, the second click selects the second occurrence, and so on.
when you clickfind everything, Excel will open a list of all occurrences, and you can click any item in the list to jump to the corresponding cell.
Excel Search: Additional Options
To make your search more specific, clickChoicesin the right corner of Excelfind a replacementdialog box, and then do one of the following:
- To search for a specific value in the current worksheet or the entire workbook, selectAn argumentlubricantworkbookwW.
- To search from left to right (row by row) in the active cell, selectin serieswI am lookingTo search from top to bottom (column by column), selectBy columns.
- To search for a specific type of data, selectformulas,valuesLubCommentswLook.
- For a case-sensitive search, seecapitalization control.
- To find cells that contain only the entered charactersfind thisselection boxMatch all cell contents.
Advice.If you want to search for a value in a range, column or row, select that range, column or row before opening itfind and replacein excel. For example, to limit the search to a specific column, first select that column and then open the filefind and replacedialogue.
Find cells with specific format in Excel
To find cells with a specific format, tapControl + Fshortcut to openfind and replacedialog box, clickChoicesand then click the buttonForm…in the upper right corner and set your options in Excelsearch formatDialog window.
If you want to find cells that match the format of another cell in the worksheet, clear all criteria infind thisclick the arrow next to itForm, selectSelect Cell Formatand click the cell with the format you want.
UseMicrosoft Excel saves the formatting options you set. If you're looking for other data in your worksheet and Excel can't find the values you know are there, clear the previous lookup's formatting options. To do this, open the filefind and replaceclick the buttonChoiceson buttonCome acrosstab, and then click the arrow next to itForm..and selectDelete search format.
Find cells with formulas in Excel
Con Excelfind and replace, you can only search formulas for a given value as explained inadditional search options in Excel. To find cells that contain formulas, use the methodgo to specialistmode.
- Select a range of cells where you want to find formulas, or click any cell in the current sheet to search the entire sheet.
- Click the arrow next to itfind and selectand then clickgo to specialist. Alternatively, you can press F5 to open the fileI willdialog box and click the buttonParticular…button in the lower left corner.
- wgo to specialistdialog box, selectformulas, then check the boxes corresponding to the results of the types you want to find and click OK:
- Numbers – Find formulas that return numeric values, including dates.
- Text: Search for types that return text values.
- Logical: Search for formulas that return logical values TRUE and FALSE.
- Errors: Find cells with error-causing formulas like #N/A, #NAME?, #REF!, #VALUE!, #DIV/0!, #NULL! and #NUM!.
If Microsoft Excel finds cells that match your criteria, they will be highlighted. Otherwise, it will display a message that no such cells were found.
Advice.to find quicklyall cells with formulas, regardless of the result of the formula, clickfind and select>formulas.
How to select and highlight all records found in a sheet
To select all occurrences of a value in a spreadsheet, open an Excel filefind and replaceenter your search term in the boxfind thisfield and clickfind everything.
Excel will list the entities it finds and you click on any occurrence in the list (or just click anywhere in the results area to move the focus there) and pressControl + Aabbreviation. This will select all occurrences found in bothfind and replacedialogue and on the sheet.
After selecting the cells, you canattractionchange the fill color.
How to use the Replace function in Excel
Here are step-by-step instructions on how to use Replace in Excel to change one value to another in a range of cells, an entire worksheet, or workbook.
Replace one value with another
To replace specific characters, text, or numbers in an Excel worksheet, usesubstituteExcel tabfind a replacementdialogue. Detailed steps are shown below.
- Select the range of cells where you want to replace the text or numbers. To replace characters throughout the worksheet, click any cell in the active worksheet.
- typeCtrl + Hshortcut to opensubstituteExcel tabfind and replacedialogue.
or go todom> tabversiongroup and clickfind and select>substitute…
if you just usedSearch in Excelmode and then just switch tosubstitutepato.
- wfind thiswrite the value you want to search in the field as wellexchangefield, type the value you want to replace.
- Finally, click on anysubstitutereplace the occurrences found one by one orreplace allreplace all entries at once.
Advice.If something went wrong and you get a different result than expected, click the buttonDispose ofbutton or pressControl + Zto restore the original values.
For additional features of Excel Replacement, click the buttonChoicesbutton in the right corner of the pagesubstituteZone. They are basically the same asExcel search optionswe just talked.
Replace the text or number with anything
To replace all occurrences of a specified value withNicoEnter the characters to searchfind thisbox, let'sexchangeempty field and click the buttonreplace allbutton.
How to find or replace a line break in Excel
Replaceline breakwith a space or other delimiter, insert a new line in the filefind thisare presented by pressingControl + J. This hash is the ASCII verification code for character 10 (line break or line feed).
when pressedControl + J, at first sightfind thisthe box will appear empty, but upon closer inspection you will notice a small blinking dot as in the screenshot below. Enter the wildcard wexchangebox, for example, a space character, and clickreplace all.
To replace a character at the end of a line, do the opposite: type the current characterfind thisbox and line break (Control + J) Wexchange.
How to change cell format in worksheet
In the first part of this tutorial, we discussed how to do this.find cells with specific formatusing the Excel Find dialog box. Excel Replace allows you to go a step further and change the format of all cells in the worksheet or the entire workbook.
- Opensubstitutein Excel's Find and Replace dialog box and click the buttonChoices
- Fencefind thisclick the arrow with the symbolFormselection buttonSelect Cell Formatand click any cell with the format you want to change.
- Fenceexchangeclick the buttonForm…and set up the new format with excelform of exchangeDialog window. or click the arrowFormselection buttonSelect Cell Formatand click any cell with the desired format.
- If you want to replace the format in thecomplete workbook, selectworkbookwWbox. If you only want to override the format on the active sheet, leave the default option (Disagreement).
- Finally, clickreplace allbutton and check the result.
UseThis method changes the formats applied manually, it will not workconditionally formatted cells.
Excel Find and replace with wildcards
Usewild cardin search criteria you can automate many find and replace tasks in Excel:
- I am usingestrella(*) to find any string. For example,M*finds"smile"I"smell".
- I am usingquestion mark(?) to find any single character. For example,grisfinds"Gris"I"Gris".
For example, to get a list of names starting with "announcement", I am using "announcement*" for the search criteria. Also note that with the default options, Excel will search for the criteria anywhere in the cell. In our case, it will return all cells that have "announcement" in any position. To avoid this, click the buttonChoicesbutton and checkMatch all cell contentsbox. This will cause Excel to return only values that begin with "announcement", as shown in the screenshot below.
How to find and replace wildcards in Excel
If you want to find actual asterisks or question marks in an Excel worksheet, type a dash (~) in front of them. For example, to find cells that contain asterisks, type ~*wfind thisbox. To find cells that contain semicolons, use~?as search criteria.
This way, you can replace all the question marks (?) in the spreadsheet with another value (in this example, the number 1):
As you can see, Excel successfully finds and replaces wildcards in both text and numeric values.
Advice.To search for tilde characters in a sheet, type a double tilde (~~) wfind thisbox.
Find and replace shortcuts in Excel
If you have been following the previous sections of this tutorial carefully, you may have noticed that Excel offers 2 different ways to interactfind and replacecommands by clicking buttons on the ribbon and using keyboard shortcuts.
Below is a quick summary of what you've learned so far, as well as a few more shortcuts that might save you a few extra seconds.
- Control+F- stand outCome acrossopen file shortcutCome acrossbookmark withfind a replacement
- Ctrl+H- stand outsubstituteopen file shortcutsubstitutebookmark withfind a replacement
- Ctrl+Shift+F4- find the previous occurrence of the price you are looking for.
- Shift+F4- find the next occurrence of the search value.
- Control+J- find or replace newline.
Find and replace in all open workbooks
As you just saw, Excel's Find and Replace feature provides many useful options. However, you can only search one workbook at a time. You can use the find and replace tool in all open workbooksAdvanced search and replace toolAbblebits plugin.
NEXTAdvanced search and replace toolFeatures make Excel search even more powerful:
- find and replace mediaall open bookslubricantchosennotebooks and worksheets.
- simultaneous searchin prices, types, hyperlinks and comments.
- Export search resultsto a new workbook with a single click.
To launch the Advanced Find and Replace add-in, click its icon on the Excel ribbon located atAblebit Tools> tabI am lookingComplex. Alternatively, you can pressCtrl + Alt + F, and even set a friend to open itControl + Fabbreviation.
The Advanced Find and Replace panel opens, where you need to do the following:
- Type the characters (text or number) you want to search forfind this
- Select which workbooks and sheets you want to search. By default, all sheets in all open workbooks are selected.
- Select the data types to search: values, formulas, comments or hyperlinks. All data types are selected by default.
In addition, you have the following options:
- ChooseThe case of the partycase-sensitive data search option.
- Choosewhole cellcheck box to find an exact and complete match, i.e. find cells containing only the characters you typedfind this
clickfind everythingand you will see a list of entries found on the pageSearching resultsZone. And now you can replace all or selected occurrences with another value or export the found cells, rows or columns to a new workbook.
If you want to try the advanced Find and Replace tool in Excel spreadsheets, you can download the evaluation version below.
Thanks for reading and I hope to see you on our blog next week. In our text tutorial, we will focus on ExcelSearch and findbut alsoREPLACEMENT and REPLACEMENTfeatures so keep watching this place.
14 Day Ultimate Suite Full Version(.exe file)
You may also be interested in this
- How to bulk find/replace in Excel
- Advanced search and quick replacements in Google Sheets
- How to find distinct and unique values in Excel
- How to identify and flag duplicates in Excel