How to use Find and Replace in Excel most efficiently

Find value in a range, worksheet or workbook

The following guidelines tell you how to find specific characters, text, numbers or dates in a range of cells, worksheet or entire workbook.

1. To begin with, select the range of cells to look in. To search across the entire worksheet, click any cell on the active sheet.
2. Open the Excel Find and Replace dialog by pressing the Ctrl + F shortcut. Alternatively, go to the Home tab > Editing group and click Find & Select > Find…
   
3. In the Find what box, type the characters (text or number) you are looking for and click either Find All or Find Next.
   

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.

Find cells with specific format in Excel

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.

Find cells with formulas in Excel

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.

1. Select the range of cells where you want to find formulas, or click any cell on the current sheet to search across the entire worksheet.
2. Click the arrow next to Find & Select, and then click Go To Special. Alternatively, you can press F5 to open the Go To dialog and click the Special… button in the lower left corner.
   
3. In the Go To Special dialog box, select Formulas, then check the boxes corresponding to the formula results you want to find, and click OK:
   • Numbers - find formulas that return numeric values, including dates.
   • Text - search for formulas that return text values.
   • Logicals - find formulas that return Boolean values of TRUE and FALSE.
   • Errors - find cells with formulas that result in errors such as #N/A, #NAME?, #REF!, #VALUE!, #DIV/0!, #NULL!, and #NUM!.

   

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.

How to select and highlight all found entries on a sheet

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.

Replace one value with another

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.

1. Select the range of cells where you want to replace text or numbers. To replace character(s) across the entire worksheet, click any cell on the active sheet.
2. Press the Ctrl + H shortcut to open the Replace tab of the Excel Find and Replace dialog.

   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.

3. In the Find what box type the value to search for, and in the Replace with box type the value to replace with.
4. Finally, click either Replace to replace the found occurrences one by one, or Replace All to swap all the entries in one fell swoop.
   

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.

Replace text or number with nothing

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.

How to find or replace a line break in Excel

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.

How to change cell formatting on the sheet

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.

• Open the Replace tab of Excel's Find and Replace dialog, and click the Options
• Next to the Find what box, click the arrow of the Format button, select Choose Format From Cell, and click on any cell with the format you want to change.
• Next to the Replace with box, either click the Format… button and set the new format using the Excel Replace Format dialog box; or click the arrow of the Format button, select Choose Format From Cell and click on any cell with the desired format.
• If you want to replace the formatting on the entire workbook, select Workbook in the Within box. If you want to replace formatting on the active sheet only, leave the default selection (Sheet).
• Finally, click the Replace All button and verify the result.

How to find and replace wildcard characters in Excel

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.