How to find and replace in Excel

  • Post author:
  • Post category:Uncategorized

The FIND feature in Excel is exceptionally useful when we have to find a value within our data and it is too time-consuming to scroll through the values one by one, cell by cell.  The REPLACE feature, on the other hand, is especially helpful when we have to make corrections in our data and replace the values we found with another value.

Figure 1.  Final result:  Find and replace

Find and Replace

Menu options

In order to access Find and Replace through Menu options:

  • Click the Home tab, Find & Select command button in Editing Tools, then choose Find or ReplaceThe Excel Find and Replace dialog box will appear.  

Figure 2.  Find and Replace in Menu options

Keyboard shortcuts

In order to access Find and Replace through shortcuts:

Find:   Ctrl + F

Replace: Ctrl + H

How to use Find and Replace

Find

Suppose we want to search for Ohio, we enter “Ohio” in the Find what textbox.  

  • Depending on our preference, we can tick either or both boxes for
    • Match case
    • Match entire cell contents

Figure 3.  Find and Replace dialog box

  • Click Find Next.  If the value is found, the cell containing that value will be selected.  

Figure 4.  Matched value is selected using FIND

  • Click Find All to view all matched values found within the sheet.  Two cells C8 and C16 are found with the word “Ohio”

Figure 5.  Find All

FIND easily searches for the value across the whole worksheet, but we can also set to find a value across the whole workbook by selecting from the drop-down menu in Within:.

Figure 6.  Within sheet or workbook selection

We can use Find and Replace in columns, worksheets or in entire workbooks.  

Replace

In order to replace the values that matched values in FIND, we click the REPLACE tab and enter new word or phrase.  

  • Suppose we want to replace Ohio with the state abbreviation OH.

We enter OH in the Replace with: textbox.  

Figure 7.  Entering the new word for replacement

  • Click Replace and notice the first matched value in C8 replaced with OH.  

Figure 8.  Ohio replaced with OH

  • We can also replace all values at once by clicking Replace All.  The two values found will automatically be replaced and Excel will show a message prompt on the number of replacements made.  

Figure 9.  Message prompt after completing replacements

Figure 10.  Output: Find and replace