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.

We have the following example data.

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:

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

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:

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:

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.

2. Enter the search term in the Find what box.
3. Type in the new term you want to replace the search term with.

4. Press Enter.

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 All replaces all the terms in one go. And displays a message confirming all the replacements have been made.

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.

It displays a wide variety of options to choose from.

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.

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.”

3. Click “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*.

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.

Press Find Next.

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

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

Isn’t that amazing? 🤩

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

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:

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

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.

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.

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, FIND, REPLACE and SUBSTITUTE functions in Excel.

Frequently asked questions

Frequently asked questions