Excel Find and Replace Within a Formula: Steps and Tips

Yes. In Excel for Microsoft 365 on Windows, press Ctrl+H, open Options if needed, set Look in to Formulas, enter the text to find and its replacement, then choose Replace or Replace All. This changes matching text inside formulas; it does not understand your intention, so check the scope and preview a small change before replacing across a workbook.

What Find and Replace Does to a Formula

A formula is stored as text that Excel interprets, and Find and Replace can search that formula text rather than only the result displayed in a cell. For example, you can change a sheet name, a range address, or a fixed text value embedded in formulas without editing every cell individually. The formula must remain valid after the change, however: Excel replaces matching characters, not a particular kind of reference or formula component.

That distinction matters. If you replace a short fragment such as B2, Excel may find that sequence wherever it occurs in formula text, including as part of a longer reference or a name. It does not know that you meant only one specific cell reference. Use a distinctive search string and inspect the formulas that will change.

How to Replace Part of a Formula

Select the cells you want to affect, if you want to limit the operation to a range. If you do not select a range, Excel searches the current worksheet by default.
Press Ctrl+H to open Find and Replace. Alternatively, use Home > Find & Select > Replace.
If the extra settings are hidden, choose Options. Set Look in to Formulas. This is the important setting when searching the formula itself rather than a displayed value.
Check Within. Choose Sheet to stay on the active sheet, or Workbook to search across the workbook. Confirm the Find what and Replace with entries.
Choose Find Next to inspect a match, Replace to change one match at a time, or Replace All to apply every match within the chosen scope. Read the completion message, then check affected formulas and results.

Find and Replace remembers settings from previous searches in some Excel sessions. Before a broad change, verify Look in, Within, and the selected range instead of assuming the dialog has reset. If you are uncertain, use Find Next and Replace for a few matches before deciding whether Replace All is appropriate.

A Worked Example: Changing a Sheet Name

Suppose formulas on a calculation sheet refer to a worksheet named Rates, and the source worksheet has been renamed Forecast. With the relevant formula cells selected, search for Rates! and replace it with Forecast!. The exclamation mark helps make the search more distinctive than the word Rates by itself.

Assume B2 on the Forecast sheet contains 100 and F1 on the calculation sheet contains 5%. With those cell values, the original formula is =Rates!B2*(1+$F$1), and its result is 105. Replacing Rates! with Forecast! changes it to =Forecast!B2*(1+$F$1); with the same values, the result remains 105. The example assumes the sheet exists and the referenced cells contain those values.

For a one-off correction, editing the sheet reference directly in the formula bar may be safer. Find and Replace becomes useful when the same reference appears in many formulas. In either case, check a sample formula and its calculated result, because formulas may not all follow the same pattern.

Choosing the Right Scope

The scope determines how many formulas may change. Excel can search the current sheet or the entire workbook, and a selected range can narrow the operation further. A workbook-wide replacement is convenient when all formulas should change consistently, but it also increases the chance of replacing an unintended match on a distant sheet.

One or a few cells: Select the cells, then use Replace or edit the formulas directly.
One worksheet: Leave Within set to Sheet and confirm that the intended sheet is active.
Several worksheets: Use Workbook only when the search text and replacement are appropriate throughout the file. Inspect results on more than one sheet.
Unsure about matches: Use Find Next to review occurrences individually before changing them.

A selection is a useful safeguard, but verify the selected cells before opening the dialog. If you selected only part of a formula table, formulas outside that range will not be updated and may then behave differently from their neighbors.

Common Problems and How to Avoid Them

Excel finds no matches. Check that Look in is set to Formulas, that the search text is present exactly as written, and that the correct sheet or workbook is in scope. A formula may use a different sheet name, an absolute reference with dollar signs, or a slightly different range. Also check whether you accidentally limited the search to a selection.

The formula changes but returns an error. A replacement can leave invalid syntax or refer to a sheet, cell, function, or named range that does not exist. Undo immediately with Ctrl+Z if the result is wrong, then inspect the formula text and correct the search pattern. Save a copy before a large workbook-wide replacement; it gives you another recovery option if later edits make the change difficult to undo.

More formulas changed than expected. The search is textual, not token-aware. A short fragment may appear inside unrelated references, sheet names, or other formula text. Undo and retry with a longer, more distinctive string, or use a narrower selection. Review the number of replacements Excel reports and inspect representative cells.

The displayed answer stays the same. A text change in a formula does not necessarily change its result. Two different references may currently contain the same value, or a formula may still calculate to the same number. Verify the formula in the formula bar as well as its displayed result.

Text, Wildcards, and Exact Matching

In Find and Replace, the asterisk (*) and question mark (?) can act as wildcard characters in searches: an asterisk can match a sequence of characters, while a question mark can match one character. A tilde (~) is used to search for a wildcard character itself. Wildcards can be useful for broad searches, but they can also make a formula replacement far less specific. For an ordinary literal fragment, avoid wildcard characters unless you intend wildcard matching.

Find and Replace is not the same as a formula-aware refactoring tool. It will not infer that one reference should change while a similar-looking name should remain untouched. Nor does it automatically update every related item in a workbook just because a formula fragment changes. Check dependent formulas, named ranges, charts, and other relevant workbook elements where the change could matter.

Find and Replace or Another Method?

Use Find and Replace when many formulas contain the same exact text and the replacement is uniform. Use direct editing when only one or two formulas need a change or when each replacement needs judgment. If the formulas were generated from a consistent template, fixing the source formula and filling it across the range may be clearer than changing many individual formulas.

For larger or repeated transformations, a carefully designed formula, a controlled data-import process, or VBA may be more appropriate. Those options take more setup and can introduce their own risks. Whatever method you choose, preserve a backup, define the intended scope, and validate the resulting formulas rather than relying only on the absence of an error message.

Excel for Mac and Excel for the Web

The steps above describe Excel for Microsoft 365 on Windows. In Excel for Mac, use the Replace command from the Edit menu or the Home tab’s Find & Select controls; keyboard shortcuts can differ by keyboard and Excel version. The same principle applies: set the search to formulas if that option is available, and check the scope before replacing.

Excel for the web also includes Find and Replace, but its dialog and available options can differ from the Windows desktop version. If you cannot choose to search formulas or control the scope as needed, open the workbook in desktop Excel and perform the replacement there. Check the resulting formulas after returning to the web version.

Frequently Asked Questions

Can I replace only part of a formula in Excel?
Yes. Use Ctrl+H, set Look in to Formulas, and replace the text fragment. Select a range first if you want to restrict the operation, and inspect matches when the text is not unique.

How do I replace a sheet name in many formulas?
Search for the old sheet name followed by an exclamation mark, then replace it with the new sheet name and exclamation mark. Check the formulas afterward and confirm that the destination sheet exists.

Why is Excel finding formula results instead of formula text?
The Look in setting may be set to Values. Open Options in Find and Replace and choose Formulas, then repeat the search.

Can I replace formulas on every worksheet at once?
Yes. Set Within to Workbook, if that option is available, and verify that the search text is safe across all sheets. For more control, work sheet by sheet or select the intended cells.

Can I undo Replace All?
In desktop Excel, Ctrl+Z usually undoes a recent Replace All operation. Undo promptly and do not rely on it as the only safeguard; save a separate copy before a large or consequential replacement.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.