If Find and Replace is not finding text inside Excel formulas, press Ctrl+H, open Options, and set Look in to Formulas; also check whether the search is limited to the selected cells or current sheet. This searches formula text rather than displayed results, so use it carefully: Replace All can change many formulas at once.
What Find and Replace does with formulas
Excel’s Find and Replace tool can search either the text stored in cells or the results those cells display. A formula cell has both: its formula, such as a SUM calculation, and its displayed result, such as 60. If Look in is set to Values, Excel searches the result, not the formula text. A search for a function name or cell reference will therefore miss it. Set Look in to Formulas when you want to find or change text that appears in a formula.
Find and Replace edits formula text; it does not understand your intention or safely rewrite only a particular kind of reference. For example, asking it to replace a cell reference can also change that same text inside a longer reference or a quoted text string. Treat Replace All as a broad text edit, not as a formula-aware refactoring tool.
How to find and replace formula text
In Excel for Microsoft 365 on Windows, follow these steps:
- Select the cells you want to search, or leave the selection cleared if you intend to search the whole sheet.
- Press Ctrl+H to open Find and Replace, then choose the Replace tab if it is not already selected.
- Choose Options to reveal the search settings. Set Look in to Formulas.
- Check Within. Choose Sheet to search the active worksheet or Workbook to search across worksheets in the workbook.
- Enter the text to locate in Find what and the new text in Replace with. Use Find Next or Find All to inspect matches before choosing Replace or Replace All.
On Excel for Mac, Control+H opens Find and Replace in many current installations; the dialog layout and available search options can differ slightly. If the shortcut does not open it, use Excel’s Edit menu to access Find and Replace. In Excel for the web, use the Home tab’s Find & Select controls or Ctrl+H, then check the available search scope and formula-search options. The web version may not expose exactly the same dialog choices as desktop Excel.
Before replacing anything, note the active sheet, selection, and Within setting. A selected range can limit the search even when you expect the entire worksheet to be searched. If the workbook scope is selected, Excel may search formulas on multiple sheets, so inspect the affected worksheets rather than assuming the change is limited to the visible one.
Check the search scope and options
The most common reason a search appears to do nothing is a mismatch between the search settings and the intended target. Check these items in the Find and Replace options:
- Look in: Choose Formulas to search formula expressions. Values searches displayed results instead. Options for notes or comments may also appear, depending on the Excel version.
- Within: Choose Sheet or Workbook according to the scope you need. A workbook-wide search can find formulas on hidden or distant worksheets as well as the active sheet.
- Selection: Excel may search only the selected cells. Click a single cell or clear a multi-cell selection if you mean to search more broadly.
- Search direction: By Rows and By Columns affect the order Excel visits cells, not which formula text it can match. They can matter when stepping through results.
- Format criteria: A previous search may have retained a formatting condition. If matches seem inexplicably limited, inspect the Format control in the dialog and clear any unwanted format criteria.
Find and Replace can retain settings from an earlier search, and those settings may not be obvious when the dialog first opens. If a search that worked before suddenly returns no results, verify the Look in choice and any formatting criteria before changing the search phrase.
Use a small example to verify the result
Suppose B2:B4 contain 10, 20, and 30, and C2 contains a total formula. With that layout, the formula in C2 is =SUM(B2:B4), which displays 60. To redirect the total to values in C2:C4, find the text B2:B4 and replace it with C2:C4, but only after confirming that the intended formulas use that exact range. If C2:C4 contain 4, 5, and 6, the edited total displays 15.
This example also shows why previewing matches matters. If other formulas contain the same range for a different purpose, Replace All will change those too. Search first, review the results, and replace one match before applying the change broadly. You can then check that the formula result and the referenced cells are what you intended.
Why a replacement can produce the wrong formula
Excel’s replacement works on matching text, not on the logical structure of a formula. A short search string may appear in multiple contexts. For example, replacing A1 can affect a reference such as $A$1 or A10 because the search text is present within those strings. It might also alter quoted text inside a formula. A formula that builds a label containing the characters A1 is still text to the replacement tool.
Be especially cautious when changing function names, sheet names, named ranges, or parts of external workbook references. A replacement can leave a formula syntactically invalid, change a reference you did not mean to touch, or make Excel interpret a name differently. Some references include dollar signs, sheet names, spaces, or workbook names, so searching for only a short fragment can have a wider impact than expected.
When the goal is to change a set of formulas in a predictable way, use distinctive search text and inspect the matches. If formulas differ in structure, editing the relevant cells individually or using a carefully designed formula may be safer than replacing a common fragment across a workbook.
Find and Replace versus changing a formula result
A frequent misconception is that replacing a displayed number changes the formula that produced it. It does not when Look in is set to Values. For example, a cell displaying 60 might contain a sum formula, a typed number, or a link to another sheet. A values search targets what Excel displays, while a formulas search targets the stored expression in formula cells. If the same result appears in many cells, a values search can find those results without revealing whether their formulas are alike.
Likewise, changing a formula’s referenced cells is not the same as changing the underlying source data. If a formula points to the wrong range, replacing the reference may fix that formula, but it does not move or correct the source values. First decide whether the problem is the formula, the data, or just the number format. For a number-format issue, Find and Replace is generally not the right fix.
Ways to reduce the risk of Replace All
Before making a workbook-wide change, save a separate copy of the file. Normal Find and Replace changes can usually be reversed with Ctrl+Z on Windows (Command+Z on Mac), but keeping a copy gives you a fallback if you save, close, or make later edits before noticing a problem. A large replacement is still worth checking immediately rather than relying on undo.
- Use Find Next or Find All first and confirm that the matches are the formulas you intend to edit.
- Start with Replace on one match, then inspect the edited formula and its displayed result.
- Use a specific range, function name, or sheet reference rather than a fragment likely to occur elsewhere.
- After replacing, search again for the old text with Look in set to Formulas to see whether any intended matches remain.
- Check for error values such as #REF! or #NAME? and recalculate if results appear stale.
If formulas appear not to update after a valid edit, check the calculation setting under Formulas > Calculation Options. With calculation set to Manual, Excel may not recalculate every formula immediately; choose Automatic if appropriate, or recalculate the workbook with Ctrl+Alt+F9 on Windows. Recalculation will not repair an incorrect replacement, so inspect the formula itself as well.
When a different approach is better
Use Find and Replace when the formula text contains a consistent fragment that genuinely needs to change in many cells. It is useful for a controlled change such as redirecting a repeated range, updating a consistently named sheet reference, or locating formulas that contain a particular function. It is less suitable when references vary, when you need to change only one argument in complex formulas, or when the same text appears in unrelated formulas.
For a small number of cells, select each cell and edit its formula in the formula bar. For a larger model with varied formulas, consider whether a shared input cell, named range, or table-based design would make future updates easier. These alternatives can reduce repeated hard-coded references, but they require understanding how the workbook is structured. Do not replace text across a workbook simply because the results look wrong; first identify the specific formulas and dependencies that produce those results.
Frequently Asked Questions
Why does Excel find a value but not the formula?
Look in is probably set to Values. Open Ctrl+H, choose Options, and set Look in to Formulas. Also confirm that the formula cell is within the selected range and that Within is set to the intended sheet or workbook.
Can I replace text inside formulas without changing the displayed values?
Yes. Choose Look in: Formulas, enter the formula fragment to find, and replace it with the intended text. The displayed result may change if the new formula refers to different cells or performs a different calculation; inspect the result after replacing.
Why does Find and Replace change only some formulas?
Some formulas may not contain the exact search text, or they may be outside the selected range or chosen scope. Differences in absolute references, sheet names, spaces, or formula structure can also prevent an exact match. Search for a distinctive fragment and inspect the formulas that were found.
Can I use wildcards when searching formulas?
Excel’s Find and Replace supports wildcard characters in searches: an asterisk matches a run of characters, and a question mark matches one character. Use a tilde before a wildcard character when you want to search for that character literally. Wildcards can broaden the matches substantially, so use Find All to review them before replacing.
Will Replace All update formulas on every worksheet?
Only if the search scope is set to Workbook. With Within set to Sheet, the search is limited to the active worksheet. A selected range can further limit what is searched, so confirm both scope and selection before choosing Replace All.
Related
- Excel Table Names: Where to Find, View, and Change Them
- Excel Weekly Meal Planner: Build a Reusable Template
- Excel Web Keyboard Shortcuts: Essential Keys and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Data Model Not Working? Diagnose and Fix the CauseIf Excel’s Data Model is not working, first check that you are using Excel for Microsoft 365 on…
- VLOOKUP Not Working in Excel? Show the Formula FixIf VLOOKUP appears in a cell instead of returning an answer, first turn off Formulas > Show Formulas…
- Excel Not Working on Your Laptop? Causes and FixesIf Excel is not working on your laptop, restart the laptop, open Excel by itself, and check whether…
- Why Some Excel Dates Are Left Aligned and How to Fix ThemIn Excel, dates that are left aligned are often text rather than real date values: Excel normally aligns…
- Excel Won’t Let Me Group Rows? Causes and FixesIf Excel will not let you group rows, first make sure you are on an unprotected worksheet with…
- Why Is My MATCH Formula Not Working in Microsoft Excel?Most MATCH formulas fail because the match type is wrong, the lookup value differs from the data, or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.