How To Find And Replace In Excel:
Wildcards, Values, And Formulas!

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Excel’s search functions are great . . . but if you aren’t using the result in another cell, you can use a more familiar tool.

The Find and Replace dialog is very powerful, especially once you get a handle on its more advanced features.

In this tutorial, we’ll show you how to find and replace data in your spreadsheet and share some tips on how to make your search more efficient.

Let’s get to it!

The Find and Replace dialog

To start, we’ll take a quick look at the Find and Replace dialog itself.

Open the dialog by pressing Ctrl + F. You can also click the Find & Select button on the far right side of the Home tab and choose Find… or Replace…

find-select

This will bring up the basic Find & Replace dialog.

find-replace-dialog

As you can see, this dialog doesn’t offer a whole lot of options (yet). But don’t let that fool you—it can be an extremely useful tool.

Kasper Langmann, Co-founder of Spreadsheeto

Clicking the Replace tab brings adds another field, and enables the find-and-replace functionality.

And clicking the Options >> button in either tab brings up a slew of extras:

find-replace-options

Get your FREE exercise file

Learning to find and replace data is easier when you have a spreadsheet to work on.

Grab our free exercise workbook to follow along with the rest of the article!

Download the FREE Exercise File

Download exercise file
Download free exercise file

How to search in Excel

The search function of the Find and Replace dialog is just like you’d expect. Open the dialog, type in your search query, and hit Enter, click Find Next, or hit Find All to run your search.

Let’s open up the example workbook and run a search to see how it works. We’ll look for a specific car manufacturer, Subaru.

Hit Ctrl + F to open the Find and Replace dialog, and type “subaru” into the search field:

search-subaru

Hit Enter or click Find Next, and Excel will scroll to the first result and highlight it for you:

subaru-found

Notice that the capitalization doesn’t matter in this case.

Kasper Langmann, Co-founder of Spreadsheeto

Now, click Find All to see how the results are displayed.

find-all

Excel displays a list of all the occurrences of your search query in the document. Click on any of the results in the list to go to that instance.

You can use this method to search for text or numbers.

Kasper Langmann, Co-founder of Spreadsheeto

Search wildcards

Wildcards are useful in formulas, but you can also use them to make your find-and-replace actions easier, too.

Use the asterisk when you’ll accept a string of any length.

For example, a search for “g*i” will return “Gething,” “Lamborghini,” and “GTI.”

The question mark will only return values that have a single character in that space. A search for “g?i” won’t return “Gething” or “Lamborghini,” but will return “GTI.”

If you ever want to search for a string with a wildcard character in it, you can tell Excel not to treat an asterisk or a question mark as a wildcard by prefacing it with a tilde.

So “g*i” has a wildcard, but “g~*i” doesn’t. The second search will only return cells that match the string “g*i.”

Kasper Langmann, Co-founder of Spreadsheeto

Advanced search options

Now that you have basic searching down, let’s take a look at some of the more advanced options you have for finding data.

Click the Options >> button in the Find and Replace dialog. You’ll see a wide variety of new choices. Here’s what they do:

  • Format… lets you narrow your search to specific cell formats (we’ll see shortly how useful this can be).
  • Within: allows you to choose to search the entire workbook, instead of a single sheet.
  • Search: sets the search to run by row or by column, changing the order in which you’ll see your search results—this can be useful if you have a massive spreadsheet and want to the search to run left-to-right instead of up-to-down.
  • Look in: tells Excel where to look for your search query (we’ll talk about this in a moment).
  • Match case makes the search case-sensitive.
  • Match entire cell contents tells Excel to only return cells that match your search query exactly, and don’t contain anything else.

Several of these options are self-explanatory, but we’ll take a look at some of the more esoteric options.

First, let’s see how the Look in menu changes our search. Click over to the second sheet in the example workbook, where you’ll see names, vehicle makes and models, and the base price, discount, and sale price of each vehicle.

It’s important to notice that the sale price is determined by a formula (in this case, it’s the base price multiplied by 1 minus the discount). That will become important. Take a look at the first number in the sale price column:

sale-price

Let’s run a standard search for the number “26,229” and see what happens.

search-not-found

Excel doesn’t find anything because the default search looks at formulas. Because “26,229” isn’t in the formula (which is =C2*(1-D1)), we don’t get a result. Let’s change the search scope to Values and run it again.

search-values

The search works perfectly now. When you search for values, you’re searching what’s displayed in the cell, and not the formula that creates it.

So if you have a spreadsheet with a lot of formulas, this is a good thing to remember.

The rest of the advanced search options, other than searching by format, are self-explanatory. If you’re not sure how one of them works, try it out and see!

We’ll go over the Format options next.

Kasper Langmann, Co-founder of Spreadsheeto

Searching with formats

Formatted cells can be very useful in Excel. Not only do they provide a visual reminder that a cell is important, but you can actually use them to narrow your search.

Let’s take a look at a quick example.

On the second sheet of the example workbook, several of the vehicles are highlighted in green. Two of them are Toyotas—but there are other Toyotas in the list. Similarly, there are highlighted cells that aren’t Toyotas.

Let’s use the Find and Replace dialog to find all of the highlighted cells that contain Toyotas. Hit Ctrl + F to open the dialog, and make sure the advanced options are visible.

You’ll also need to make sure that Values is selected in the Look in: dropdown.

Click on the arrow next to Format to bring up a dropdown menu:

find-format

Click Choose Format From Cell… and your cursor will turn into a cell picker:

search-not-found

Click on one of the highlighted cells, and the preview pane in the Find and Replace dialog will now show the format of that cell.

preview-format-search

Then, type “toyota” into the search box and run the search.

The results will only include the two cells that are highlighted and contain “Toyota” in the displayed values.

Non-highlighted cells that contain “Toyota” won’t be included, and neither will highlighted cells that contain other makes of vehicle.

Remember that searching by format includes the cell format as well—so if your cell is formatted as currency, this will only search currency cells.

Kasper Langmann, Co-founder of Spreadsheeto

Without using the cell picker, you can search for any specific combination of formatting elements, as well.

As you can imagine, this can be a very powerful tool for search huge spreadsheets and workbooks!

Finding and replacing values

Now that you’ve come this far, finding and replacing values in Excel will be a breeze. All the options are the same—you just have an extra field in the search window:

find-replace-options

All you need to do is enter what you’d like to replace the search term with and press Replace to go through the results one-by-one, or Replace All to replace all the values at once.

That’s it.

You’ll notice that the Replace with box also has a formatting option. If you replace values, you can also choose to format the new cells in specific ways.

Let’s take a look.

Open up the first sheet in the example workbook. We’ll say that we accidentally wrote “Acura” when we should have written “Ford,” and we want to make a note of that.

Hit Ctrl + F to open the Find and Replace dialog, and open the advanced options in the Replace tab.

We’ll add “acura” to the Find box, and “Ford” to the Replace box.

find-replace-acura-ford

Let’s say we want to highlight the new cells to remind us that we need to get in touch with the owners.

Click Format next to the replace box, and change the fill to red.

find-replace-format

Finally, click Replace All.

found-formatted

Every instance of “Acura” has been replaced with “Ford” and highlighted in red.

Wrapping things up…

Using find and replace in Excel is a lot like using it in other Microsoft Office apps. But it comes with some extra power that you can use to refine your search or make tweaks to your replacements.

Once you’ve mastered this, you’ll be able to make large-scale edits in your spreadsheets much faster. And that puts you one step closer to Excel mastery.

2019-03-28T15:36:19+00:00