Find & Replace

Imagine inheriting a spreadsheet with 2,000 rows where every instance of "United States" needs to be changed to "US" — and someone also used "USA" in some rows. Doing that manually would take an hour and you would almost certainly miss some. Find & Replace does it in about 10 seconds. It is one of those tools that feels almost too simple, but the more advanced options hidden inside it make it genuinely powerful for data cleaning and bulk editing.

Opening Find & Replace

There are two entry points depending on what you need:

The Find & Replace Dialog

Here is what the dialog looks like. The top field is what you are searching for, the bottom field is what you want to replace it with. Once you are ready, you have two options — Replace replaces the current match one at a time, and Replace All replaces every match at once.

After clicking Replace All, Excel tells you how many replacements were made. Always check this number — if it is higher or lower than expected, it is a sign that your search term is matching something unintended.

The Options Panel — Where It Gets Powerful

Click the Options >> button and the dialog expands to reveal a set of powerful search controls. Most people never use these, but they are what make Find & Replace genuinely useful for data cleaning rather than just simple text swaps.

Let's go through the options that matter most in practice:

Within: Workbook — by default, Find & Replace only searches the current sheet. Switching to Workbook searches every sheet in the file simultaneously. Extremely useful when you have multi-sheet workbooks and need to make a global change.

Match case — when ticked, the search becomes case-sensitive. Searching for "north" with Match Case ticked will not find "North". Leave it unticked for most situations.

Match entire cell contents — without this, searching for "on" would match "London", "Monday", "conversion" and anything else containing those two letters. Tick this box when you want to match only cells where the entire content is exactly your search term — useful when replacing codes or IDs that might appear as substrings elsewhere.

Look in: Values vs Formulas — by default Excel searches Values (what you see in the cell). Switch to Formulas to search the actual formula text. Useful if you need to find every formula that references a particular cell or function.

Find All

Instead of stepping through matches one by one with Find Next, Find All lists every matching cell at once at the bottom of the dialog. You can click any result in the list to jump straight to that cell. You can also select all matches at once by pressing Ctrl + A in the results list — which selects all matching cells in the sheet so you can format them, delete them, or apply any change to all of them simultaneously.

Replacing with Nothing — Deleting Text

If you leave the Replace with field completely empty and click Replace All, Excel replaces every match with nothing — effectively deleting that text from every matching cell. This is a clean way to remove unwanted characters, prefixes, or suffixes from a column without writing any formulas.

Find what:     "USD "       (with a trailing space)
Replace with:  (leave empty)

Before:   USD 42000    USD 15000    USD 8200
After:    42000        15000        8200

Replace Formatting

One of the most underused features in the dialog is the ability to find and replace based on formatting rather than text. Click the Format button next to either field to specify a cell format to search for or replace with.

For example, you might want to find every cell with a yellow fill and change it to a green fill. Or find every cell formatted in red font and clear the colour. Click Format next to "Find what" → choose the formatting to search for. Click Format next to "Replace with" → choose the formatting to apply. Then click Replace All.

Using Wildcards in Search

Wildcards let you search for patterns rather than exact text. Excel supports two wildcard characters in Find & Replace:

Wildcards work in the Find what field. They are especially useful when cleaning messy imported data — for example, finding all cells that start with a certain prefix regardless of what comes after it.

Find what:    *Ltd*
Replace with: (empty)

Removes "Ltd" and surrounding text from any cell containing it
— useful for cleaning company names imported from an external system

Go To Special — Find by Cell Type

Closely related to Find is Go To Special, which selects cells based on their type or property rather than their content. Press Ctrl + G (or F5) then click Special to open it.

Visible Cells Only deserves a special mention. When you copy filtered data normally, Excel copies the hidden rows too. Select Visible Cells Only first (shortcut: Alt + ;), then copy — and only the visible rows are copied. This is the correct way to extract filtered results.

Next up — Basic Formulas, where we tie everything together and you start writing your own calculations using SUM, AVERAGE, COUNT, MIN, MAX, and more.