Excel Find and Replace not working: 8 fixes

1. Only part of the sheet is searched

If more than one cell is selected when you press Ctrl+F or Ctrl+H, Excel searches only the selection. That's handy on purpose and maddening by accident: you selected a column a minute ago, and now Find can't see the product two columns over.

Click a single cell, then search again. Do the opposite when you want to limit a Replace: select just the column first, so replacing "Cable" can't touch a note somewhere else.

2. The value is on another sheet

Within: Sheet searches the active sheet only. Click Options and set Within to Workbook to search every sheet. Find All then lists each match with its sheet and cell, and clicking a line jumps there.

Grouped sheets work the other way. If several tabs are selected ([Group] in the title bar), Replace changes all of them. Ungroup first unless that's what you want.

1. Press Ctrl+F and click Options.
2. Set Within to Workbook.
3. Click Find All to see every match on every sheet.

3. Formatted numbers and dates aren't found

Plug, EU has 1200 in stock. With a thousands separator it shows as 1,200, but the cell stores 1200. With Look in set to Formulas, Excel searches what's stored, so 1,200 finds nothing and 1200 works. With Look in set to Values, it searches what the cell shows, so 1,200 works.

Dates are the same: the cell stores a date serial number and shows a format. Search with Look in: Values and type the date the way it's displayed. The TEXT function shows the displayed version of a value, which tells you what to type.

4. Formula results can't be found or replaced

Stock value in column E is a formula, =C2*D2. The cell shows 540, but it holds the formula. Find with Look in: Formulas searches the formula text, so 540 isn't found. Switch Look in to Values to find results.

Replace has no Values option: its Look in list only offers Formulas, because Excel can't overwrite a result without overwriting the formula. To change results, change the inputs, or copy the column and paste it as values first if you really want static numbers.

The flip side: Replace does change text inside formulas. Replacing "C" with "D" across a sheet rewrites every formula that refers to column C. Select only the cells you mean to change before replacing anything short.

5. "Match entire cell contents" or Match case is still on

Find remembers the options from your last search. With Match entire cell contents ticked, searching Cable finds nothing, because no cell is exactly "Cable". With Match case ticked, cable doesn't find Cable.

Open Options and untick both unless you need them. The reverse matters for Replace: replacing 12 with 15 without Match entire cell contents also turns 120 into 150 and 1200 into 1500. Tick it when you replace whole numbers.

6. Wildcards find (or replace) too much

In Find, * means any characters and ? means one character, the same as in COUNTIF. "Cable*" finds the three cables. But it also means a search for * matches every cell, and Replace All with * in Find what wipes the lot.

To find a real asterisk, like the footnote mark on "Hub 4-port*", put a tilde in front: ~*. The same goes for ~? and ~~. The two COUNTIF formulas below behave the way Find does, so you can test a pattern before a Replace All.

7. Spaces, line breaks and hidden cells

Text pasted from a web page often has non-breaking spaces between words. They look like spaces but aren't, so "Laptop stand" with a normal space won't match. On Windows, type one in Find what by holding Alt and typing 0160 on the number pad, or replace them with a formula as the remove spaces guide shows.

For line breaks inside a cell (Alt+Enter), click in Find what and press Ctrl+J. The box looks empty but holds the break. Replace it with a space to join two-line cells.

Searching Values can skip cells in hidden rows and columns. Unhide everything, or search with Look in: Formulas, which also looks in hidden cells.

8. Replace fails on a protected sheet, or use a formula

On a protected sheet, Replace stops with a message that the cell is on a protected sheet. Unprotect it with Review > Unprotect Sheet, or ask whoever set the password.

When you want the original kept, replace with a formula instead. SUBSTITUTE replaces text in a copy, so you can check the result before pasting it back as values.