Find and Replace Across All Sheets

What it does

Excel's built-in Ctrl + H has a "Within: Workbook" option, but it's buried, easy to miss, and doesn't tell you how many replacements happened per sheet or in total. This macro replaces text across every worksheet in the active workbook in one shot, asks four short questions (find, replace, match case, whole cell), then reports a clean total at the end. Great when you need to update a vendor name, a fiscal year, or a typo that's scattered through a 30-tab planning file.

Real-world example

Picture a financial planning analyst at a mid-size logistics company in late March. She maintains a master forecasting workbook with 24 tabs: one per region per quarter, plus a few summary tabs. The CFO walks in and says the company is rebranding from "TransLogix" to "Translogix Group" effective next Monday. Could she please make sure every tab uses the new name in headings, footers, scenario labels, and any text references.

Her first instinct: open Find and Replace, hope she remembers to flip "Within" to Workbook (it defaults to Sheet), check Match Case, click Replace All, and pray she's done. But she's been burned before. Last quarter she replaced "FY24" with "FY25" and accidentally caught a couple of cells that read "FY244" and got mangled to "FY255". So now she runs the replace, then walks every sheet to spot-check, which is 24 sheets at maybe 90 seconds each. About 35 minutes of clicking through tabs.

With this macro she clicks Run, types "TransLogix", types "Translogix Group", says no to match case (the brand is consistent enough), says yes to whole cell only (so it never butchers a longer string that happens to contain it), and the macro reports back: "Replaced 87 occurrence(s) across 24 sheet(s)." That number sanity-checks her expectation. Total elapsed: about 30 seconds, plus another 5 minutes spot-checking the high-stakes summary tabs.

On a 24-tab brand-rename, expect about 30 minutes saved per pass. The bigger win is the count at the end — knowing exactly how many cells changed catches the cases where the count is suspicious (zero where you expected hits, hundreds where you expected dozens).

How to use

First time? See How to run our tools for the 30-second setup.

1. Open the workbook you want to edit. The macro operates on every sheet of the active workbook.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick FindReplaceAcrossSheets, click Run.
3. Type the text to find.
4. Type the text to replace it with. Leave blank if you want to delete the matching text.
5. Type Y if you want to match case (case-sensitive), anything else for case-insensitive.
6. Type Y if you want to match the whole cell only (so partial matches are skipped), anything else for partial match.
7. The macro reports the total number of replacements made across all sheets.

FAQ

Will it search inside formulas, or only displayed values?

Only displayed values. The macro searches with LookIn: xlValues, which means it matches what you see in cells, not the underlying formula. If you need to rewrite formulas (for example, change a sheet reference inside every formula), use a separate tool or Excel's built-in Find and Replace with LookIn: Formulas.

Does it touch hidden sheets?

Yes. Hidden sheets are still part of the workbook and the macro processes them. If you have an archive sheet you don't want changed, unhide it temporarily, move it to a separate workbook, run the macro, then move it back. Or right-click the tab and choose "Very Hidden" before running, but that requires the VBA editor.

Can it search across multiple workbooks at once?

No. The macro operates on the active workbook only. If you need to update many separate files, open them one at a time and run the macro on each. Multi-workbook find/replace is a feature we'll consider adding if folks ask for it.

What if my find text contains special characters like commas or quotes?

Plain text only. The InputBox accepts any characters except line breaks, so commas, quotes, and parentheses all work. Wildcards like * or ? are treated literally (not as wildcards). This is intentional — wildcard search/replace is too easy to misuse and corrupt data with.

Can I undo it?

Yes. Ctrl/Cmd + Z right after running undoes all replacements at once. As with any bulk edit, work on a copy of the file if you cannot afford to lose the original. The macro runs entirely offline. Your data never leaves your machine.