How to Use Find and Replace in Excel (Including Wildcards)

A supplier changes its name. A product code needs updating everywhere. You need to find one customer in a workbook with a dozen sheets. Find and Replace does all of this in seconds, and it has some powerful options hidden behind a button most people never click.

1. Press Ctrl + F (on a Mac, Command + F).
2. Type what you're looking for in Find what.
3. Click Find Next to jump to each match in turn, or Find All to see a list of every match.

Find All is the most useful. It lists every matching cell with its sheet, address, value and formula. Click any result to jump to it. Press Ctrl + A inside the list to select all the matching cells at once, then you can colour them or delete them in one go.

1. Press Ctrl + H.
2. Type the old text in Find what.
3. Type the new text in Replace with.
4. Click Replace All, or use Find Next and Replace to check each one first.

Excel tells you how many replacements it made. If it's wrong, press Ctrl + Z to undo.

Tip: Select a range first to search only inside it. With a single cell selected, Excel searches the whole sheet.

Click Options >> to reveal:

• Within: Sheet / Workbook: search every sheet in the file at once.
• Search: By Rows / By Columns: the order Excel moves through the cells.
• Look in: Formulas / Values / Comments / Notes: search inside formulas, or search what cells display. Use Values to find a number that's the result of a formula.
• Match case: find "Apple" but not "apple".
• Match entire cell contents: find cells that contain only your text.

This option prevents nasty surprises. Replacing 1 with 2 without it changes 10 to 20, 100 to 200 and 2021 to 2022. Tick Match entire cell contents and only cells containing exactly 1 are changed.

Wildcards let you search for patterns:

Wildcards are great with Replace. For example, find (*) and replace with nothing to remove everything in brackets, brackets included.

Because * and ? are wildcards, searching for them finds everything. Put a tilde in front: search for ~* to find actual asterisks, or ~? for question marks.

You can find cells by how they look, and change their formatting in bulk:

1. Press Ctrl + H and click Options.
2. Click Format… next to Find what. Choose the formatting to find, such as a yellow fill. Or click the arrow next to Format and choose Choose Format From Cell… and click an example cell.
3. Click Format… next to Replace with and choose the new formatting.
4. Leave both text boxes empty and click Replace All.

Every yellow cell is now, say, green, and bold.

To replace line breaks inside cells with a space: in Find what, press Ctrl + J (it looks empty, but it's there), type a space in Replace with, and click Replace All.

"We couldn't find what you were looking for." Check Within is set to Workbook if the data is on another sheet, Look in is set correctly (Values vs Formulas), and Match entire cell contents isn't ticked by mistake. Also check for a leftover format in the Format box. Click the arrow and choose Clear Find Format.

It replaced too much. Undo with Ctrl + Z, then tick Match entire cell contents or Match case and try again.

It won't replace in some cells. Those cells are on a protected sheet, or the matches are in formula results rather than typed values.

Ctrl + F to find, Ctrl + H to replace, and always click Options to search the whole workbook, match whole cells or use wildcards. Find All plus Ctrl + A is a brilliant way to select every match at once.