How to Find and Replace in Excel: Ctrl+F, Ctrl+H, Wildcards and the Whole Workbook

Those shortcuts and menu paths come from Microsoft's own help page. The dialog looks simple, but the Options button hides most of what makes it useful, and one of those options behaves differently on the Find tab and the Replace tab.

Find and Replace shortcuts and menu paths

To do this	Shortcut	Menu path
Find	Ctrl+F	Home > Editing > Find & Select > Find
Replace	Ctrl+H	Home > Editing > Find & Select > Replace
Search the whole workbook	Not needed	Options, then Within: Workbook
Find a real question mark	Not needed	Type ~? in Find what

If you like keeping your hands on the keyboard, my list of Excel shortcuts has the rest of the time-savers.

How to find something

In the Find what box, type the text or numbers you want to find, or pick a recent search from the arrow list. Then select Find All or Find Next.

Find Next jumps to one match at a time. Find All lists every occurrence at the bottom of the dialog, and selecting one in the list selects its cell. You can also sort the Find All results by clicking a column heading, which is handy when matches are spread over several sheets.

How to replace something

Type the text or numbers you want to change in Find what, and the new value in Replace with. Then choose:

• Replace All to replace every occurrence of your search in one go.
• Replace to update one occurrence at a time, so you can check each one.

Replace All is fast and it's thorough: it changes every match, including the ones you didn't know were there. That's exactly why I check the list first.

Wildcards: finding patterns, not exact text

Three characters change how the search box works:

Type	Finds	Microsoft's example
? (question mark)	Any single character	s?t finds sat and set
* (asterisk)	Any number of characters	s*d finds sad and started
~ (tilde) before ?, * or ~	The character itself	fy91~? finds fy91?

The asterisk is the powerful one, and the risky one. In Microsoft's example s*d finds started as well as sad, so a Replace All on a wildcard search can change far more cells than you meant to. The tilde is the fix for the opposite problem: when the data contains a real question mark or asterisk, put a tilde in front of it so Excel stops treating it as a wildcard.

The Options button: where the useful settings live

Select Options to open the full dialog. These are the settings it adds:

Option	Choices	What it does
Within	Sheet or Workbook	Searches the current sheet or every sheet in the file
Search	By Rows (default) or By Columns	Sets the order Excel works through the cells
Look in	Formulas, Values, Notes or Comments	Decides what Excel reads: the formula, the result, or the notes and comments
Match case	On or off	Makes the search case sensitive
Match entire cell contents	On or off	Finds only cells that contain exactly what you typed, and nothing else

Match entire cell contents is the one that saves the most mistakes. Without it, replacing 10 with 20 also changes 100 and 2010. With it ticked, only cells that hold exactly 10 are touched.

The Look in catch

Here's the detail most people miss. Formulas, Values, Notes and Comments are all available on the Find tab, but only Formulas is available on the Replace tab.

In practice that means you can find a value that a formula displays, but you can't use Replace to change it, because Replace works on what's typed into the cell rather than on the result. If Find spots something that Replace then refuses to touch, this is almost always why.

Finding cells by their formatting

To search by formatting, select Format and make your choices in the Find Format dialog. To find only cells with a specific format, delete anything in the Find what box and choose the format from an example cell. It's a quick way to track down every cell someone's highlighted, without scrolling.

Find and Replace on a Mac

Microsoft describes two methods in Excel for Mac. One is the Find and Replace dialog box, opened with Control+F or from Home > Find & Select > Find. The other is the Search bar in the ribbon. In the dialog, the same Within, Search and Look in options apply.

Worth bookmarking alongside this: how to use COUNTIFS in Excel.

Replacing text with a formula instead

Microsoft's own tip: you can also replace text with formulas. The SUBSTITUTE function, and the REPLACE and REPLACEB functions, do the job inside a cell, which keeps the original data intact next to the cleaned version.

Find and Replace is also one of the fastest ways to tidy messy data. If your problem is stray spaces, my guide to removing spaces in Excel goes further, and if it's gaps in a list, see deleting blank rows.

A safe way to replace, step by step

1. Press Ctrl+F to find, or Ctrl+H to replace.
2. Type what you're looking for in Find what, adding wildcards if you need them.
3. Select Options to search the whole workbook, in formulas, with Match case or Match entire cell contents.
4. Select Find All and read the list of every match.
5. Fill in Replace with, and choose Replace or Replace All.

My verdict

Run Find All first and read the list, then Replace All. It takes ten seconds and it's saved me from more broken spreadsheets than I'd like to admit. Tick Match entire cell contents whenever you're replacing numbers, be careful with the asterisk, and if a whole sheet's affected, save a copy before you press the button.

There's more on this in Excel Agent Mode.

Frequently asked questions

What is the shortcut for find and replace in Excel?

Ctrl+H opens Replace and Ctrl+F opens Find. Both are also under Home, Editing, Find and Select.

How do I search the whole workbook?

Select Options in the Find and Replace dialog and set Within to Workbook instead of Sheet.

Can I use wildcards in Find and Replace?

Yes. A question mark finds any single character, an asterisk any number of characters, and a tilde finds a real ?, * or ~.

Why can't I replace a value that Find can see?

Look in offers Values on the Find tab, but only Formulas on the Replace tab, so Replace works on what's typed in the cell.

How do I find and replace on a Mac?

Use the Find and Replace dialog with Control+F, or the Search bar in the ribbon. Microsoft says the same options apply.