Excel Tutorials
Find and Replace looks like a two-box dialog and is hiding a genuinely powerful search tool behind an Options button most people never click. This lesson covers wildcards, scope, searching formulas versus values, replacing formatting and the settings that stop a Replace All from wrecking your data, using the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
Find locates every cell containing what you type. Replace does the same search and then swaps each match for something else. Between them they handle the bulk edits that would otherwise mean scrolling through a few thousand rows making the same correction over and over.
The reason it deserves a full lesson is that the default settings are deliberately broad. Out of the box, Excel matches your search term anywhere inside a cell and ignores capitalisation, which is helpful when you are hunting for something and dangerous when you are replacing it. Replacing North with N will also rewrite Northeast as Neast unless you change one setting.
Click Options >> and the dialog expands to reveal scope controls, case sensitivity, whole-cell matching, wildcard support and the ability to search by formatting rather than text. Those controls are the difference between a clean bulk edit and an afternoon of repair work.
Step-by-step, matching the video above.
Ctrl+F opens the Find tab of the Find and Replace dialog. Type what you are looking for and click Find Next to jump to each match in turn, or Find All to list every match at once.
Ctrl+H opens the same dialog on the Replace tab, which adds a Replace with box. You can also click between the two tabs once the dialog is open.
The collapsed dialog hides most of its power. Clicking Options >> exposes Match case, Match entire cell contents, Within, Search, Look in and the two Format buttons.
Within switches between the current Sheet and the entire Workbook. Search chooses whether Excel scans by rows or by columns, which only changes the order matches are found in.
Formulas searches the underlying cell contents, Values searches the displayed result, and Comments or Notes searches annotations. This is the setting that decides whether searching for SUM finds your formulas or nothing at all.
Match case makes the search case-sensitive, so Total no longer matches total. Match entire cell contents requires the whole cell to equal your search term rather than merely containing it.
Find All lists every match with its sheet, cell address and value at the bottom of the dialog. Click any row to jump straight to that cell. This is the safety check that stops a bad Replace All.
Replace changes the current match and moves to the next, letting you skip any you do not want. Replace All changes every match in scope immediately and reports how many it changed.
Three characters turn Find and Replace from an exact-text tool into a pattern-matching one.
Searching for North* finds North, Northeast, Northern Region and North America. Use it when you know how a value starts or ends but not what comes in between.
Q?-2026 matches Q1-2026 through Q4-2026 but will not match Q-2026 or Q12-2026, because each question mark stands for precisely one character.
To find a genuine asterisk in your data, search for ~*. The tilde tells Excel to treat the next character as ordinary text rather than a wildcard, and ~? works the same way.
An asterisk in the Replace with field is inserted as a literal asterisk. Patterns only apply to what you are searching for, not what you are writing back.
| What You Type | Settings | What It Matches |
|---|---|---|
| North | defaults | North, Northeast, Northern Region |
| North | Match entire cell contents | North only |
| North* | wildcards | Every value starting with North |
| Q?-2026 | wildcards | Q1-2026, Q2-2026, Q3-2026, Q4-2026 |
| ~* | escaped wildcard | A literal asterisk in the cell |
The first two rows are the difference that catches people out most often. With the default settings, replacing North with N also turns Northeast into Neast. Ticking Match entire cell contents restricts the replacement to cells that contain nothing but the word North, which is almost always what you actually meant.
The two Format buttons let you search by how a cell looks rather than what it says.
Click Format next to the Find box and pick a fill colour, font, border or number format. Leave the Find what box empty and Excel finds every cell with that formatting regardless of its contents.
Set a format on both the Find and Replace sides to swap every yellow-filled cell to green, or every bold red font to plain black, across the whole sheet in one action.
Inside the Format dialog, the dropdown arrow next to the Format button offers Choose Format From Cell, which picks up the formatting of a cell you click rather than making you rebuild it by hand.
Format settings persist in the dialog for the rest of your session. If a later search mysteriously returns no results, an old format filter is usually still active, and Clear Find Format resets it.
The tasks Find and Replace handles faster than any formula.
Search for two spaces and replace with one, then repeat until Excel reports zero replacements. For leading and trailing spaces, TRIM is the better tool, but Replace All handles doubled internal spaces instantly.
Leave the Replace with box completely empty and every match is deleted. This is how you strip out currency symbols, stray hyphens or thousands separators from a text import.
With Look in set to Formulas, replacing an old sheet or range name updates every formula referring to it at once, rather than editing them individually.
Hold Alt and type 0010 on the numeric keypad in the Find what box to enter a line break character, then replace it with a comma and a space to flatten multi-line addresses into one line.
This is the classic. Replacing North with N also converts Northeast to Neast and Northern to Nern. Tick Match entire cell contents whenever you mean the whole value.
The setting persists between searches. A replacement you intended for one sheet quietly runs across every sheet in the file, including the ones you had forgotten were in there.
With Look in on Values, Excel searches the displayed results, so searching for VLOOKUP returns nothing even though the formulas are right there. Switch Look in to Formulas.
Format criteria stay set for the whole session. When a search that should obviously find something returns no results, click the Format dropdown and choose Clear Find Format.
Searching for * matches every non-empty cell, because Excel reads it as a wildcard. Type ~* to search for a literal asterisk character.
Find All shows you the exact list of what is about to change, with cell addresses. Thirty seconds of checking beats undoing a replacement you discover three saves later.
Ctrl+Z does reverse a Replace All in the current session, but not after the file has been saved and closed. On a large or important replacement, save a copy first.
Bulk-editing the same value across ten tabs means the value is living in ten places it should not be.
Free 14-day trial. No credit card required.
Ctrl+F opens Find and Ctrl+H opens Replace. Both open the same dialog, just on different tabs, and you can switch between them once it is open.
Click Options, then tick Match entire cell contents. Excel will then only replace cells whose full value equals your search term, leaving longer values that merely contain it untouched.
Yes. An asterisk matches any number of characters, a question mark matches exactly one, and a tilde before either one lets you search for that character literally.
Put a tilde in front of it. Searching for ~* finds a literal asterisk and ~? finds a literal question mark, instead of treating them as wildcards.
The Look in setting is probably on Values, which searches displayed results rather than cell contents. Change Look in to Formulas and the search will match the formula text.
Click Options and change Within from Sheet to Workbook. Remember to change it back afterwards, because the setting stays put for later searches.
Yes. The Format buttons beside each box let you match on fill colour, font, border or number format, and replace one format with another across the sheet.
Type the character in Find what and leave Replace with completely empty. Replace All then removes every occurrence.
Ctrl+Z reverses it while the workbook is still open in the same session. Once the file is saved and closed the change is permanent, so save a copy before a large replacement.
Select the range first, then open Find and Replace. Excel restricts the operation to the selected cells only.
Find All lists every match at the bottom of the dialog with its sheet name, cell address and value. Clicking a row jumps to that cell, which makes it a useful preview before replacing anything.
Every business starts with a spreadsheet. Updoot is where you scale past it, records link themselves automatically.
Start Your Free Trial →