Find and replace in Excel

Excel, like most common applications, supports Find and Replace functionality.

Find in Excel

To open the Find and Replace dialog box to search for, do one of the following:

• On the Home tab, in the Editing group, click the Find & Select button:
  

  From the Find & Select dropdown list, choose Find...:

  
• Click Ctrl+F.

Excel opens the Find and Replace dialog box with the Find tab opened:

In the Find and Replace dialog box, on the Find tab:

• In the Find what field, type a text you want to find
• Click the Find Next button to show the next search result
• Click the Find All button to display search results in the list at the bottom of the dialog box. For example:
  

  You can click an item in the list to navigate to the cell in context. To select all the cells in the list, first, select any single item in the list, then press Ctrl+A to select them all.

• Click the Options >> button to refine the search criteria:
  
  • Click the Format... button to find the cell with needed formatting:
    
    • Select Format... from the list to set formatting options like Number formatting, Alignment (left, right, center, etc.), Font, colors, and Protection:
      

      For example, to find all cells with Accounting format marked in green:

      
    • Select Choose Format From Cell to select a cell with the format you want to search. Excel changes the cursor to the Picker and proposes to select the cell with the format you would like to find:
      

      After selecting a cell, Excel returns to the Find and Replace dialog box to continue customization.

    • Select Clear Find Format if needed to clear selected formats.
  • In the Within dropdown list, select where you want to search: only in the active sheet (by default) or in the entire workbook:
    
  • In the Search dropdown list, set the search order for data: by rows (by default) or by columns:
    
  • In the Look in dropdown list, specify the type of data where you want to search: in formulas (by default), in values, in notes or comments:
    

  For example:

  • If the Formulas is selected, searching for 4 doesn't find a cell with a formula that returns 4, unless the formula itself contains 4:
    
  • If the Values is selected, searching for 4 find both cells with the value 4 and with the formula result of 4:
    
• The Match case option:
  • Select the Match case option if the searched text must match exactly. For example, searching OfficeToolTips does not locate officetooltips and Officetooltips.
  • Unselect the Match case option to find all variants of the searched text, regardless of case.
• The Match entire cell contents option:
  • Select the Match entire cell contents option if the cell must contain only the searched text and nothing else.

    Note: When using wildcards, an exact match is not required.

  • Unselect the Match entire cell contents option if the cell can contain any data with the searched text.

Wildcards in Excel

The Find and Replace dialog box supports two wildcard characters:

Find and replace

To open the Find and Replace dialog box in the search and replace mode, do one of the following:

• On the Home tab, in the Editing group, click the Find & Select button:
  

  From the Find & Select dropdown list, choose the Replace... command:

  
• Click Ctrl+H.

Excel opens the Find and Replace dialog box with the Replace tab active:

In the Find and Replace dialog box, on the Replace tab:

• In the Find what field, type a text you want to find
• In the Replace with field, enter the text you want to replace the found text
• Click the Replace All button to replace all search results
• Click the Replace to replace only one result and go to the next.

Note: Other options are the same as the Find tab, see more about them above.