How to Use Find and Replace in Excel: Full Guide (2024)

Find and Replace is one of the most common yet most resourceful features of Excel.

Searching through hundreds of thousands of rows is definitely not the best thing to do. And to replace something within these rows is even worse.

So Find and Replace lets you find a particular word or phrase in seconds regardless of the volume of the data.

It surely is one of the most needed functions of Excel. If you are yet to use this feature, jump right into the guide below to know all about it 🧐

You can also download our sample workbook for this article here if you want to practice along.

How to use Find and Replace

Using the Find and Replace feature is pretty simple. Press CTRL F to open the dialog box. Or select it from the Editing group.

Excel displays the find and replace options.

We have the following example data.

Example data set on the active worksheet.

Suppose we want to find Emily, the name of an employee working in a company. To do that, we will enter her name in the Find dialog box as:

Using the find Tab to find a name.

Once you type the name, press Enter. Excel will highlight the cell containing the result.

All search results shown in a list.

Pro Tip!

Note that the name isn’t capitalized here. That’s because, in the find feature, capitalization doesn’t matter 😉

The Find Next button moves you to the next instance containing the search item by highlighting the cell.

The Find All button shows a list of all the occurrences where the search item occurs. Like this:

Results shown in free Excel sheet

You can then choose whichever value you want, and it will direct you to the cell.

If you want to view all the cells at the same time, press CTRL A or manually select the entire list. Excel will highlight all the cells simultaneously:

Results shown in free Excel sheet

Replace data with Find and Replace

Now, let’s see how the replace function works

Suppose we want to find the position of Accountant in this data. And replace it with Auditor.

To do this:

  1. Select Replace tab from the Find and Replace dialog box. Or press CTRL H. You can also open it from the Find & Select option under the Editing group.
Using the replace window.
  1. Enter the search term in the Find what box.
  2. Type in the new term you want to replace the search term with.
Text to replace search item with.
  1. Press Enter.
Text replaced.

Note that we entered capitalized search terms. That’s because Replace needs to locate the exact text string. And then replace it with some other term.

Kasper Langmann, Microsoft Office Specialist

Similar to the Find feature in Excel, Replace tab also has the Replace Next and Replace all options.

The Replace Next option replaces each search term one by one.

The Replace next option only replaced one entry.

The Replace All replaces all the terms in one go. And displays a message confirming all the replacements have been made.

Excel makes all the replacements. question mark.

And that’s how you use the Find and Replace feature. Pretty easy, no? 😀

Advanced settings of Find and Replace

Now that you know all the basics of Find and Replace, let’s look at some advanced features it has to offer.

To open the advanced functions of the Find feature, click the Options button in the dialog box.

Choosing Options button.

It displays a wide variety of options to choose from.

The option button shows a plethora of information.

Note that the list of options for the Find and Replace tab is the same. The only difference is that in Replace tab, you get an additional format option for the replace box.

Let’s study all these options in detail below.

Format: The format option lets you specify the format of the search item. Click the Format option at the top to select a format.

Selecting the Format option.

You can also choose the format for your text string from a particular cell. For that,

  1. Click on the drop-down arrow next to Format.
  2. Delete the existing format by clicking “Clear Find Format.”
Clearing Find format
  1. Click “Choose Format From Cell”
Choose format from cell.

The cursor appears with a dropper.

Just click on the cell whose format you want to copy, and click Replace all. The dialog box also shows a *Preview*.

Find Tab shows preview

Within: The within option lets you search a specific text string throughout the entire workbook 😵

So even if you don’t remember where a certain piece of text is in your workbook, you can simply search for it. This feature is actually very helpful when you have to search for a single term from extensive data.

Match Case: The match case option enables case sensitivity. This means that Excel will search for a specific term with the exact capitalization used.

Say we search “hr” in lowercase instead of uppercase and put a checkmark on the ‘Match Case’ option.

Lowercase position

Press Find Next.

Excel will show a warning message because we didn’t capitalize the position properly.

Excel warning message.

If we write the same search term in uppercase, Excel searches and shows the result as:

Match case showed the exact result.

Isn’t that amazing? 🤩

Search: The search option lets you select the order in which you want to search.

Search options.

If you want to look for the search item row by row, select “by row”. And if you want to search column by column, select the “by column” option.

Match Entire Cell Contents: This option restricts Find and Replace to look only for characters you have entered in the box. If you even miss a period, Excel will show a warning message.

Say, we put a checkmark on the ‘Match entire cell contents‘ option. And type in the name “John,” whereas, in the data set, the correct name is “John O.

This is what will happen:

Excel warning message.

And if we add “O.” to the same box, the result becomes:

Adding the correct name.

Excel returns the correct result. This tool is great if your spreadsheet contains a large amount of data.

Look-in: The look-in option lets you specify the type of data of your search item.

The Find tab offers three types, namely, Formulas, Values, and comments. The Replace tab, on the other hand, uses only one type of data, i.e., Formulas.

If you only want to look for formulas, you can find them using the Go To Special option from Find & Select.

Go to Special to replace text and formulas

It offers a plethora of options and you can set the options as per your choice. You can use it to find other items too.

Formulas option similar to wildcard characters.

Just select the options and Excel will automatically apply them on your next search.

That’s it – Now what

And Tada! That’s how easy and fun it is to use Excel’s Find and Replace tools 🤗

Not only can you find and replace a particular text string in seconds, but you can also specify its format, location, order, and case sensitivity.

Undoubtedly, the Find and Replace feature of Excel is very powerful. But if you want to become an Excel master, there’s so much more you need to learn.

If you are new to this spreadsheet software, we suggest you begin learning Excel’s built-in functions; IF, SUMIF and VLOOKUP.

You can learn these superb functions for FREE in my 30-minute Excel course sent straight to your inbox.

Other resources

The Find and Replace feature in Excel is great. Especially when it comes to making tweaks and narrowing down your search criteria.

The function is easy to understand and use. But with a little more practice, you will be able to master this and make large-scale changes in your data in seconds.

If you like this article, we are sure you’d love to read more. Some related articles include the SEARCH, FINDREPLACE and SUBSTITUTE functions in Excel.

Frequently asked questions

Frequently asked questions

You can launch the Find and Replace dialog box by pressing CTRL + F.

Or you can find it in the Find & Select Menu under the Editing group from the Home Tab. If you want quick access to the Replace feature, you can use the keyboard shortcut CTRL + H.

If you prefer using formulas to find and replace items in Excel, you should try the SUBSTITUTE function. It is used when you know the exact characters of the text to be replaced. SUBSTITUTE function is a good alternative to Replace.