Everyone knows Ctrl+F. Almost nobody clicks Options on the dialog it opens — and behind that button sits one of Excel’s most capable tools, quietly able to sweep a whole workbook, hunt by format, and repair a thousand cells in a click. Find & Replace is a foundations lesson with power-tool reach, which is exactly why it earns its place on the path early.

Find, properly

The Options that change the game:

  • Within: Workbook — search every sheet at once. The answer to “where did I put that customer?” across a multi-tab file, and the Find All list below it is clickable — a results panel that jumps you cell to cell.
  • Look in: Formulas vs Values — the distinction you already understand: search what cells hold or what they show. Hunting a hard-typed 1042 that should be a formula? Formulas. Hunting what the reader sees? Values.
  • Match entire cell contents — finds Cork but not Corkscrew; the difference between surgical and sloppy.
  • Match case — off by default, occasionally everything.

And the two wildcards, which work here and in lookups alike: * for any run of characters, ? for exactly one. B?rne catches Byrne and Barne; *Ltd catches everything ending Ltd. To search for a literal asterisk or question mark, prefix a tilde: ~*.

Replace: bulk repair, with the safety catch

Replace is Find with consequences, and it does honest cleaning work at speed: the rogue . in dub. before the region sums, a renamed product across four sheets, double spaces to single (the poor cousin of TRIM, but in-place). Two of its best tricks are barely known:

  • Replace with nothing strips characters wholesale — find £, replace with empty, and pasted-from-web text-numbers become summable. (Cleaner than retyping; still check the column sums after.)
  • Replace formats — set a format in Find, another in Replace, leave the text boxes empty: every manually-yellowed cell becomes un-yellowed. This is how you unpick years of hand-painted highlighting on the way to rules that mean something.
Replace All: "dub." → "Dublin" (Within: Workbook) dub. Dublin dub. Dublin dub. Dublin …and the safety catch: 1 Find All first — READ the list of what will change 2 then Replace All — and check the count it reports 3 Ctrl+Z reverses the whole sweep if the count surprises you
One pass, every variant, whole workbook. The discipline is the order: see the list, then pull the trigger, then read the receipt.

The safety catch matters because Replace All is Remove Duplicates’ cousin: fast, silent, and happy to change things you forgot matched. dub also lives inside Dubrovnik. So the routine is always: Find All first and read what’s about to change; entire cell contents on when the value stands alone; Replace All; then read the count — “47 replacements” when you expected 30 is a finding, not a success. One Ctrl+Z reverses the sweep while you work out why.

One boundary keeps the tool honest: Find & Replace edits this data, once. The moment a replace becomes monthly ritual — every export needs the same dub. fixed — it stops being a foundations move and becomes a recorded step in the recipe, where it replays itself and nobody has to remember. Same instinct as ever: twice by hand is fine; the third time is automation’s job.

Ctrl+F, then Options, then the tilde when the data fights back. The dialog you’ve used a thousand times had a second life waiting the whole time.