Excel: Find and Replace
Excel Find and Replace

When working with a lot of data in Excel, it can be difficult and time consuming to locate specific information. You can easily search your workbook using the Find feature, which also allows you to modify content using the Replace feature.

Optional: Download our practice workbook.

Watch the video below to learn more about using Find and Replace.

To find cell content:

In our example, we'll use the Find command to locate a specific department in this list.

  1. From the Home tab, click the Find and Select command, then select Find from the drop-down menu.
    2. Selecting the Find feature
  2. The Find and Replace dialog box will appear. Enter the content you want to find. In our example, we'll type the department's name.
  3. Click Find Next. If the content is found, the cell containing that content will be selected.
    4. Clicking Find Next to locate content
  4. Click Find Next to find more instances or Find All to see every instance of the search term.
    5. Selecting Find All to locate every instance of the search term
  5. When you are finished, click Close to exit the Find and Replace dialog box.
    6. Clicking close to exit the dialog box

You can also access the Find command by pressing Ctrl+F on your keyboard.

Click Options to see advanced search criteria in the Find and Replace dialog box.

Clicking options to see advanced search criteria

To replace cell content:

At times, you may discover that you've repeatedly made a mistake throughout your workbook (like misspelling someone's name) or that you need to exchange a particular word or phrase for another. You can use Excel's Find and Replace feature to make quick revisions. In our example, we'll use Find and Replace to correct a list of department names.

  1. From the Home tab, click the Find & Select command, then select Replace... from the drop-down menu.
    2. Selecting Replace from the drop-down menu
  2. The Find and Replace dialog box will appear. Type the text you want to find in the Find what: field.
  3. Type the text you want to replace it with in the Replace with: field, then click Find Next.
    4. Clicking find next
  4. If the content is found, the cell containing this content will be selected.
  5. Review the text to make sure you want to replace it.
  6. If you want to replace it, select one of the replace options. Choosing Replace will replace individual instances, while Replace All will replace every instance of the text throughout the workbook. In our example, we'll choose this option to save time.
    7. Clicking replace all
  7. A dialog box will appear, confirming the number of replacements made. Click OK to continue.
    8. Clicking ok
  8. The selected cell content will be replaced.
    9. The selected content has been replaced
  9. When you're finished, click Close to exit the Find and Replace dialog box.
    10. Clicking close to exit the dialog box
Generally, it's best to avoid using Replace All because it doesn't give you the option of skipping anything you don't want to change. You should only use this option if you're absolutely sure it won't replace anything you didn't intend it to.

Challenge!

  • Open our practice workbook.
  • Click the Challenge tab in the bottom-left of the workbook.
  • Crystal Lewis got married and changed her last name to Taylor. Use Find and Replace to change Crystal's last name from Lewis to Taylor. Be careful to only change Crystal's last name!
  • Find and replace Bio with Biology. Be careful not to change the Biomedical Engineering major!
  • Use Find and Replace All to replace the Physics major to Physical Science.
  • When you're finished, your worksheet should look like this:
    • Clicking close to exit the dialog box
Find and Replace Challenge Result

Common Questions about Find and Replace

What is the keyboard shortcut for Find?

You can quickly open the Find dialog box by pressing Ctrl+F on your keyboard. To open the Replace tab directly, you can press Ctrl+H.

What is the difference between "Find Next" and "Find All"?

Find Next jumps to the first cell that matches your search term. Find All opens a list at the bottom of the dialog box showing every single cell reference that contains the search term.

When should I avoid using "Replace All"?

You should avoid Replace All if you are not 100% sure where the data exists. It changes every instance immediately without reviewing them. For example, replacing "Bio" with "Biology" might accidentally change "Biomedical" to "Biology-medical".

How do I use advanced search options?

Click the Options button inside the Find and Replace dialog box. This expands the window to let you search by case sensitivity (Match case), search within formulas vs. values, or search the entire workbook vs. the active sheet.

Can I replace specific formatting instead of text?

Yes. By clicking the Options button in the Find and Replace dialog, you can click the "Format..." button to search for specific cell colors or fonts and replace them with different formatting.

Continue to Next Lesson: Checking Spelling →