How to Find and Replace Across All Sheets in Excel

The 30-tab rebrand problem

The CFO walks in on a Friday afternoon. The company is rebranding from TransLogix to Translogix Group effective Monday. Could you make sure every tab of the master forecasting workbook uses the new name. There are 24 tabs.

Excel's Find and Replace dialog has a "Within: Workbook" option that does exactly this, but it's buried, easy to miss, and doesn't tell you how many replacements happened per sheet. Here are the real options.

TL;DR — Key takeaways

• Excel's built-in Find and Replace has a hidden "Within: Workbook" option. The default is "Sheet", which is why this is easy to get wrong.
• The built-in dialog reports a total but not per-sheet counts, and missing the "Workbook" toggle is a common mistake.
• A VBA macro runs the replacement across every sheet and reports the total, with options for case-sensitive and whole-cell matches.
• Hidden sheets are still part of the workbook and get processed by both Excel's built-in and the macro.

Method 1: Excel's built-in (with the hidden toggle)

The built-in dialog has the option, but you have to click an extra menu to find it.

1. Press Ctrl + H to open Find and Replace.
2. Click Options >> to expand the dialog (this is the step most people miss).
3. In the Within dropdown, change Sheet to Workbook.
4. Type your find and replace text.
5. Click Replace All.

Excel reports a total count when done.

The catch: the Within dropdown defaults to Sheet every time. Forget to switch it and you've replaced only on the active sheet, which you might not notice until later.

Method 2: VBA loop (programmatic)

For full control, a small VBA loop:

Sub ReplaceEverywhere()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Cells.Replace What:="OldText", Replacement:="NewText", _
                         LookAt:=xlWhole, MatchCase:=False
    Next ws
End Sub

Drop this in a module, edit the find/replace text, run.

The catch: every time you want a different replacement, you edit the code. Not user-friendly for non-VBA folks.

Method 3: The free VBA macro (interactive, with options)

Download Find and Replace Across All Sheets. Free .xlsm with one macro.

Steps:

1. Open the workbook you want to edit.
2. Press Alt + F8, pick FindReplaceAcrossSheets, click Run.
3. Type the find text.
4. Type the replace text.
5. Choose case-sensitive (Y/N).
6. Choose whole-cell match (Y/N).
7. The macro applies the replacement across every sheet and reports the total count.

The whole-cell match option is the safety feature most people forget they want. Without it, replacing FY24 with FY25 would also catch FY244 and turn it into FY255 (probably not what you want).

A common scenario: brand name change

Replace TransLogix with Translogix Group across a 24-tab forecasting workbook.

With the macro:

1. Open the workbook.
2. Run the macro.
3. Find: TransLogix. Replace: Translogix Group. Case-sensitive: N. Whole-cell: Y.
4. Macro reports "Replaced 87 occurrence(s) across 24 sheet(s)."
5. Spot-check a few high-stakes summary tabs.

The whole-cell-match option (Y) protects against the macro accidentally turning TransLogix Inc into Translogix Group Inc. The total count is your sanity check: if the number is wildly different from what you expected, investigate.

Method 4: When you have many find/replace pairs

If you have 30 substitutions to apply (vendor name normalizations, account code mappings), running Find and Replace 30 times is tedious. The Bulk Find and Replace From Table macro reads a 2-column lookup table and applies every pair to your data range in one pass.

This is a different tool for a related job. The Find and Replace Across All Sheets macro is for "one find/replace pair, every sheet." The Bulk From Table macro is for "many find/replace pairs, one data range."

What about formulas?

The macro searches xlValues, which means it matches what you see in cells, not the underlying formula. If a formula returns "Acme Co" because it pulls from a lookup table, the macro doesn't change the formula. Only displayed text values get rewritten.

If you need to rewrite formulas (changing a sheet reference inside every formula across the workbook), use Excel's built-in Find and Replace with LookIn: Formulas set in the Options dialog.

What about hidden sheets?

Hidden sheets are still part of the workbook and get processed. 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 use Excel's built-in dialog with sheet-level scope.

Frequently asked questions

Does the macro support wildcards like * or ?

No. The InputBox treats wildcards as literal characters. So a find of Acme* only matches the literal string Acme*. Wildcards in find/replace are easy to misuse and corrupt data with, so the macro's design avoids them.

Can it match newlines or tabs in cell content?

Not directly through the InputBox (which doesn't accept Enter or Tab keys). For cells with embedded newlines, use the built-in Find and Replace dialog with Ctrl + J (which inserts a newline character in the find field).

Will it search inside cell comments or notes?

No. The macro searches cell values only. Comments and notes are separate from cell values and require different VBA code (cell.Comment.Text).

What if my find or replace text contains a comma or quote?

Plain text only, so commas and quotes work as literal characters. The InputBox accepts any printable characters except line breaks.

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 it's critical.

What to do next

After a workbook-wide replacement, generate an index sheet so you can navigate the result with List All Sheet Names. For projects with many find/replace pairs (account remaps, vendor normalizations), Bulk Find and Replace From Table is the right next step.