by Svetlana Cheusheva, updated on
In this tutorial, you will learn how to use Find and Replace in Excel to search for specific data in a worksheet or workbook, and what you can do with those cells after finding them. We will also explore the advanced features of Excel search such as wildcards, finding cells with formulas or specific formatting, find and replace in all open workbooks and more.
When working with big spreadsheets in Excel, it's crucial to be able to quickly find the information you want at any particular moment. Scanning through hundreds of rows and columns is certainly not the way to go, so let's have a closer look at what the Excel Find and Replace functionality has to offer.
Below you will find an overview of the Excel Find capabilities as well as the detailed steps on how to use this feature in Microsoft Excel 365, 2021, 2019, 2016, 2013, 2010 and older versions.
The following guidelines tell you how to find specific characters, text, numbers or dates in a range of cells, worksheet or entire workbook.
When you click Find Next, Excel selects the first occurrence of the search value on the sheet, the second click selects the second occurrence, and so on.
When you click Find All, Excel opens a list of all the occurrences, and you can click any item in the list to navigate to the corresponding cell.
To fine-tune your search, click Options in the right-hand corner of the Excel Find & Replace dialog, and then do any of the following:
Tip. If you want to find a given value in a range, column or row, select that range, column(s) or row(s) before opening Find and Replace in Excel. For example, to limit your search to a specific column, select that column first, and then open the Find and Replace dialog.
To find cells with certain formatting, press the Ctrl + F shortcut to open the Find and Replace dialog, click Options, then click the Format… button in the upper right corner, and define your selections in Excel Find Format dialog box.
If you want to find cells that match a format of some other cell on your worksheet, delete any criteria in the Find what box, click the arrow next to Format, select Choose Format From Cell, and click the cell with the desired formatting.
Note. Microsoft Excel saves the formatting options that you specify. If you search for some other data on a worksheet, and Excel fails to find the values that you know are there, clear the formatting options from the previous search. To do this, open the Find and Replace dialog, click the Options button on the Find tab, then click the arrow next to Format.. and select Clear Find Format.
With Excel's Find and Replace, you can only search in formulas for a given value, as explained in additional options of Excel Find. To find cells that contain formulas, use the Go to Special feature.
If Microsoft Excel finds any cells that meet your criteria, those cells are highlighted, otherwise a message will be displayed that no such cells have been found.
Tip. To quickly find all cells with formulas, regardless of the formula result, click Find & Select > Formulas.
To select all occurrences of a given value on a worksheet, open the Excel Find and Replace dialog, type the search term in the Find What box and click Find All.
Excel will display a list of found entities, and you click on any occurrence in the list (or just click anywhere within the results area to move the focus there), and press the Ctrl + A shortcut. This will select all found occurrences both on the Find and Replace dialog and on the sheet.
Once the cells are selected, you can highlight them by changing the fill color.
Below you will find the step-by-step guidelines on how to use Excel Replace to change one value to another in a selected range of cells, entire worksheet or workbook.
To replace certain characters, text or numbers in an Excel sheet, make use of the Replace tab of the Excel Find & Replace dialog. The detailed steps follow below.
Alternatively, go to the Home tab > Editing group and click Find & Select > Replace…
If you've just used the Excel Find feature, then simply switch to the Replace tab.
Tip. If something has gone wrong and you got the result different from what you'd expected, click the Undo button or press Ctrl + Z to restore the original values.
For additional Excel Replace features, click the Options button in the right-hand corner of the Replace tab. They are essentially the same as the Excel Find options we discussed a moment ago.
To replace all occurrences of a specific value with nothing, type the characters to search for in the Find what box, leave the Replace with box blank, and click the Replace All button.
To replace a line break with a space or any other separator, enter the line break character in the Find what filed by pressing Ctrl + J. This shortcut is the ASCII control code for character 10 (line break, or line feed).
After pressing Ctrl + J, at first sight the Find what box will look empty, but upon a closer look you will notice a tiny flickering dot like in the screenshot below. Enter the replacement character in the Replace with box, e.g. a space character, and click Replace All.
To replace some character with a line break, do the opposite - enter the current character in the Find what box, and the line break (Ctrl + J) in Replace with.
In the first part of this tutorial, we discussed how you can find cells with specific formatting using the Excel Find dialog. Excel Replace allows you to take a step further and change the formatting of all cells on the sheet or in the entire workbook.
Note. This method changes the formats applied manually, it won't work for conditionally formatted cells.
The use of wildcard characters in your search criteria can automate many find and replace tasks in Excel:
For example, to get a list of names that begin with "ad", use "ad*" for the search criteria. Also, please keep in mind that with the default options, Excel will search for the criteria anywhere in a cell. In our case, it would return all the cells that have "ad" in any position. To prevent this from happening, click the Options button, and check the Match entire cell contents box. This will force Excel to return only the values beginning with "ad" as shown in the below screenshot.
If you need to find actual asterisks or question marks in your Excel worksheet, type the tilde character (~) before them. For example, to find cells that contain asterisks, you would type ~* in the Find what box. To find cells that contain question marks, use ~? as your search criteria.
This is how you can replace all questions marks (?) on a worksheet with another value (number 1 in this example):
As you see, Excel successfully finds and replaces wildcards both in text and numeric values.
Tip. To find tilde characters on the sheet, type a double tilde (~~) in the Find what box.
If you have been closely following the previous sections of this tutorial, you might have noticed that Excel provides 2 different ways to interact with Find and Replace commands - by clicking the ribbon buttons and by using the keyboard shortcuts.
Below there is a quick summary of what you've already learned and a couple more shortcuts that may save you a few more seconds.
As you have just see, Excel's Find and Replace provides a lot of useful options. However, it can search only in one workbook at a time. To find and replace in all open workbooks, you can use the Advanced Find and Replace add-in by Ablebits.
The following Advanced Find and Replace features make search in Excel even more powerful:
To run the Advanced Find and Replace add-in, click on its icon on the Excel ribbon, which resides on the Ablebits Utilities tab > Search group. Alternatively, you can press Ctrl + Alt + F, or even configure it to open by the familiar Ctrl + F shortcut.
The Advanced Find and Replace pane will open, and you do the following:
Additionally, you have the following options:
Click the Find All button, and you will see a list of found entries on the Search results tab. And now, you can replace all or selected occurrences with some other value, or export the found cells, rows or columns to a new workbook.
If you are willing to try the Advanced Find and Replace on your Excel sheets, you are welcome to download an evaluation version below.
I thank you for reading and hope to see you on our blog next week. In our text tutorial, we will dwell on Excel SEARCH and FIND as well as REPLACE and SUBSTITUTE functions, so please keep watching this space.
Ultimate Suite 14-day fully-functional version (.exe file)
Table of contents