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
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 Replace. The Excel Find and Replace dialog box will appear.
Figure 2. Find and Replace in Menu options
In order to access Find and Replace through shortcuts:
Find: Ctrl + F
Replace: Ctrl + H
How to use Find and Replace
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.
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