How to Find Invalid Formulas in Excel and Fix Errors

Errors & fixes · Updated November 20, 2026

To find invalid formulas in Excel, select the worksheet and choose Home > Find & Select > Go To Special, select Formulas and Errors, then choose OK. Excel selects formula cells that return errors. For formulas that look wrong but show no error, use Formulas > Error Checking and inspect the formula bar, references, and results. Save a copy first if the workbook is important; the checks below help separate visible errors from formulas that calculate an incorrect answer.

What You’ll Need

  • A copy of the workbook, or permission to make changes to it. Save a separate copy before editing formulas across many cells.
  • Microsoft Excel for Microsoft 365 on Windows for the menu paths in this guide. Excel for Mac has similar formula-auditing tools, though ribbon layout and keyboard shortcuts may differ.
  • A little context about what the spreadsheet should calculate. Excel can flag an error value, but it cannot always tell whether a valid formula uses the wrong business rule.

Step-by-Step

  1. Save a working copy and identify the area to check. Use File > Save As to make a copy if you may need to undo a series of corrections or if the workbook contains important data. Note which sheets, tables, and columns contain calculations. If the workbook is large, check one sheet or a meaningful range at a time so the results are easier to review. You do not need to select a range before using Go To Special; without a selection, Excel checks the current worksheet. If you select a range first, Excel limits the check to that selection.
  2. Select formula cells that return errors. On Windows, go to Home > Find & Select > Go To Special. In the dialog, choose Formulas, clear the other result types if you want to narrow the selection, and leave Errors selected. Choose OK. Excel highlights formula cells whose current results are error values, such as #DIV/0!, #N/A, #NAME?, #NUM!, #REF!, or #VALUE!. If Excel reports that no cells were found, that only means it did not find formula errors in the checked area; it does not prove every calculation is correct. On Mac, look for Go To Special under the Home tab’s Find & Select controls; wording and placement can vary slightly by Excel release.
  3. Review each selected cell rather than replacing errors automatically. Click a selected cell and read the formula bar to see the complete formula. Check the cell’s inputs, referenced rows or columns, and any copied formulas nearby. The error label is a clue, not a complete diagnosis. For example, #REF! commonly means a referenced cell or range was deleted, while #NAME? can indicate a misspelled function or an unrecognized name. Record the cell address and likely cause before changing anything, especially if the formula feeds other sheets or reports.
  4. Run Excel’s Error Checking tool for guided explanations. Select a cell that shows an error, then choose Formulas > Error Checking in the Formula Auditing group. Read the explanation and available choices, such as showing the formula bar or tracing references. Use the tool to investigate, not to accept a suggested fix without checking it. Depending on the error and workbook settings, Excel may offer actions that change a formula or ignore an error. If a dialog offers Ignore Error, use it only when you have confirmed that the flagged result is intentional.
  5. Show formulas on the sheet to spot unusual entries. Choose Formulas > Show Formulas to display formulas in cells instead of their calculated results. Compare formulas down a column or across a row. A formula that should move from one row to the next may repeat the same reference, skip a row, or point to a different column. On Windows, Ctrl+` (the grave-accent key, often beside 1) toggles this view; the ribbon command is useful if your keyboard layout makes that shortcut awkward. Click Show Formulas again to return to normal results. Widen columns if the displayed formulas are cut off.
  6. Search for formulas when you suspect a specific pattern. Press Ctrl+F on Windows, choose Options if needed, set Look in to Formulas, and search for a reference, function name, or equals sign. Use Find All to list matches and click a result to jump to its cell. Searching the entire workbook can help locate formulas that use a particular sheet or named range. Search results are not a full formula audit: a matching formula may be correct, and a formula with a different spelling or structure may not match. On Mac, use Command+F to open Find; check the available search options for your Excel version.
  7. Inspect formulas that are inconsistent with their neighbors. In a calculation column, compare the formula pattern in several rows, including the first and last rows of the range. For example, with values in B2:B6 of 10, 12, 8, 15, and 5, a total placed in B7 can use =SUM(B2:B6), which returns 50. If a similar total elsewhere omits the first or last row, it may calculate without an error but still be wrong. Select a suspicious formula and choose Formulas > Trace Precedents to highlight cells it uses. Choose Remove Arrows afterward to clear the tracing lines.
  8. Check formulas that Excel treats as text. A cell that displays an equals sign and formula text instead of a result may be formatted as Text, or the entry may begin with an apostrophe. Select the cell and inspect the Number Format on the Home tab. If appropriate, change Text to General, then edit the cell and press Enter so Excel evaluates the entry. Do this carefully: changing the format alone may not convert existing text into a working formula. Check the result and compare it with neighboring calculations.
  9. Test the repair and recheck the range. Make one correction at a time, then verify the result against a known input or an independent calculation. For a fixed set of sample values, confirm that the answer makes sense and that copied formulas use the intended relative or absolute references. Run Go To Special again after repairs to see whether any formula errors remain. If the workbook has several sheets, repeat the check on each relevant sheet; the command works on the current sheet or selected range, not as a single automatic audit of every sheet.

Common Mistakes

  • Treating every error value as a broken formula. An error may be the expected result for missing data or an unavailable lookup. Confirm what the calculation is intended to do before changing the formula or hiding the error.
  • Assuming that no error means the formula is right. Excel can calculate a formula successfully even when it references the wrong range, uses an incorrect tax rate, or leaves out a row. Check the logic and compare the result with a known example.
  • Repairing only the first cell in a repeated calculation. A copied formula can be wrong in one row while the cells above and below are fine. Check the full column or range after making a fix, including boundary rows.
  • Ignoring relative and absolute references. When copied, a reference such as A1 normally shifts. A reference with dollar signs stays fixed in the specified row or column. If a formula is copied across a table, verify that each reference moves or stays fixed as intended.
  • Using Find as if it understands formula logic. Find locates matching text; it does not identify every incorrect calculation. Choose Look in Formulas, and use other checks as well.
  • Overwriting formulas with displayed values. Copying a visible error or result over its formula can remove the calculation and make later updates impossible. Keep the original formula until you understand the cause and have a recoverable copy.

Tips

  • Start with Go To Special to find formula cells that currently return errors, then use Show Formulas and formula comparison to look for plausible but incorrect calculations. These methods find different problems.
  • Review one category of errors at a time. For example, investigate every #REF! result first, then check #NAME? and #VALUE! cells. Grouping similar symptoms can reveal a shared cause, such as a deleted column or a renamed worksheet.
  • Use Formulas > Trace Precedents to see inputs to a selected formula, and Trace Dependents to see formulas that rely on it. These tools are especially useful before changing a cell used in totals or summaries.
  • For a complex formula, select the cell and choose Formulas > Evaluate Formula. Step through the calculation to see how Excel evaluates parts of the expression. This can expose a wrong reference or unexpected intermediate value. The button may be unavailable for some formula types or workbook features.
  • Make sure the calculation mode is not set to Manual if results appear stale. On Windows, check Formulas > Calculation Options > Automatic. Recalculating will update results, but it will not repair incorrect logic. In Excel for Mac, look for Calculation options on the Formulas tab; exact placement can vary.
  • Keep a short note of the intended rule beside a complex calculation or in workbook documentation. Clear expectations make it easier to decide whether an unusual result is a genuine error or an intentional exception.

Troubleshooting

Go To Special says no cells were found. Confirm that Formulas and Errors were selected, and that you are on the sheet you mean to check. Clear any selection if you want to search the whole current sheet. If the cells contain formula text stored as text, they will not be counted as formulas that return errors; inspect their number format and entry as described above. Also check whether the visible issue is a warning indicator rather than an error value.

The cell looks wrong but has no error label. Compare its formula with a known-good neighboring cell, verify the input range, and use Trace Precedents or Evaluate Formula. A wrong but valid reference often produces an ordinary number, so the error-selection command will not find it. Test the formula with a small, known example and check whether the result matches the intended rule.

The formula shows as text or does not calculate after editing. Check whether the cell is formatted as Text and whether an apostrophe or leading space appears before the equals sign. Set the format to General, edit the entry, and press Enter. If the workbook uses Manual calculation, set calculation to Automatic or recalculate with F9 on Windows. On Mac, the recalculation shortcut can differ by keyboard settings; use the Formulas tab or Excel Help to find the command for your version.

A repaired formula still returns an error. Read the new error value; correcting one problem can reveal another. Check for deleted references, misspelled function or range names, incompatible data types, empty inputs, and formulas that refer back to themselves. If Excel reports a circular reference, inspect the formula chain and decide whether the loop is accidental. Do not enable iterative calculation simply to suppress a circular-reference warning unless the workbook is deliberately designed to use it.

The formula appears correct, but the result does not update. Confirm that calculation is Automatic, then recalculate and check the formula’s actual inputs. If the formula depends on an external workbook, a data connection, or a recently changed named range, verify that source is available and current. A stale or unavailable source can affect the output without indicating a syntax problem.

Frequently Asked Questions

Can Excel find every incorrect formula automatically?
No. Excel can select formula cells that return error values and flag some inconsistencies, but it cannot know every intended calculation. A valid formula can still use the wrong range or logic, so compare formulas with the worksheet’s purpose and known results.

What is the quickest way to find formula errors on one sheet?
Choose Home > Find & Select > Go To Special, select Formulas and Errors, and choose OK. The selected cells are formula cells currently returning errors on the active sheet or in the range you selected.

Does Go To Special find errors in every worksheet?
No. It checks the current sheet or the selected range. Repeat the command on other relevant sheets. For a large workbook, keep a checklist of sheets so you do not overlook hidden or less frequently used calculation areas.

Why does a formula have a green triangle but no error value?
The triangle is an error-checking indicator, not proof that the formula is invalid. Excel may flag a formula that differs from nearby formulas or a number stored as text. Select the cell and review the warning options, then confirm the intended calculation before choosing to ignore or correct it.

Can I find formulas that have been entered as text?
Yes, but they are not included in Go To Special’s formula-error selection because Excel is treating them as text. Search with Find set to Look in Formulas or inspect the cells directly, then check whether the cell is formatted as Text or the entry begins with an apostrophe.

Will fixing a formula error change other cells?
It can. Other formulas may depend on the cell you change, so check its dependents and verify important totals or summaries afterward. Save a copy before broad edits, and avoid replacing formulas with values unless that is specifically what the workbook requires.

Related

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.