Audit Formulas in Excel: Find Errors and Trace Cells

Formulas & functions · Updated November 21, 2026

To audit formulas in Excel for Microsoft 365 on Windows, select a formula cell and choose Formulas > Formula Auditing > Trace Precedents to see which cells feed it. Use Trace Dependents to find formulas that rely on it, and use Evaluate Formula or Error Checking to investigate a result that looks wrong.

What formula auditing means

Formula auditing is the process of checking how a worksheet calculates its results. It helps you follow links between cells, inspect a formula one calculation at a time, spot errors, and verify that similar rows use formulas consistently. Excel’s auditing tools reveal relationships and potential problems; they do not decide whether a formula reflects the right business rule or whether the source data is trustworthy. That judgment still requires understanding what the workbook is supposed to calculate.

Auditing is useful when a total changes unexpectedly, a report has an error value, a formula has been copied across many rows, or a workbook has been inherited from someone else. It is also a sensible review step before sharing a spreadsheet used for reporting or operational decisions. A displayed number can look plausible even when a formula omits a row, points to the wrong sheet, or uses an inappropriate reference.

A quick formula-auditing workflow

Start with the cell whose result you need to understand, then work outward through its inputs and downstream uses. A practical first pass is:

  1. Select the result cell and read the formula in the formula bar. Check its ranges, sheet names, and reference types.
  2. Choose Formulas > Formula Auditing > Trace Precedents. Follow the arrows to the cells used by the selected formula.
  3. Use Evaluate Formula if the calculation has several parts or if you cannot see where its result goes wrong.
  4. Run Formulas > Formula Auditing > Error Checking, then investigate each reported issue rather than accepting every suggestion automatically.
  5. Compare the formula with neighboring cells and check whether its references change in the intended way from row to row.
  6. Use Trace Dependents if you need to learn which totals, charts, or other calculations may be affected by changing an input.

For example, with monthly amounts in B2:B6 and a total in B7, the intended total formula in B7 is =SUM(B2:B6). If those five amounts are 12, 18, 20, 25, and 35, the result is 110. Tracing precedents from B7 should point to the amount cells included in the total. If B6 was accidentally excluded, the displayed result would be 75; the arrow and formula range help expose the omission.

Trace precedents and dependents

Precedents are cells referenced by the selected formula. Dependents are cells whose formulas refer to the selected cell. In Excel for Windows, both commands are on the Formulas tab in the Formula Auditing group. Select one or more relevant cells, choose Trace Precedents or Trace Dependents, and inspect the arrows Excel draws. A solid arrow generally connects cells on the same worksheet. A reference to a different worksheet can be represented by a dotted arrow and a small worksheet icon; double-clicking an auditing arrow can open a Go To list of referenced cells.

These tools are most helpful when used in stages. Trace precedents on a final total to find its direct inputs. Select one of those inputs and trace its precedents in turn if it is itself calculated. To understand the impact of changing an assumption, trace dependents from that assumption and continue through the chain. Choose Formulas > Formula Auditing > Remove Arrows when you are finished, so old arrows do not clutter the sheet or confuse a later review.

Arrows show cell references, not every possible influence on a result. A formula may depend on a named range, a table, an external workbook, or a value supplied by a function. Some dependencies are not straightforward to follow visually, especially across complicated models. Read the formula bar and inspect the referenced ranges as well as the arrows.

Evaluate a formula one step at a time

When a formula is long or nested, choose Formulas > Formula Auditing > Evaluate Formula with the formula cell selected. The dialog highlights part of the expression and lets you evaluate successive pieces. This is useful for checking whether Excel is using the value you expect at each stage, and for locating the point where an intermediate calculation differs from your expectation. Use the dialog’s Evaluate control to advance; available controls can vary according to the expression being evaluated.

Suppose a result combines a rate, a quantity, and a condition. Rather than guessing which part caused a surprising result, inspect the inputs, then step through the expression in the Evaluate Formula dialog. Check whether a comparison evaluates as TRUE or FALSE, whether a referenced cell contains a number or text, and whether a function returns an error before the outer calculation runs. If the formula uses volatile or external data, remember that a refreshed value can differ between checks.

Evaluate Formula is an inspection aid, not a substitute for testing the calculation against known cases. For a complicated model, write down a small set of input combinations and the expected outputs, then compare those expectations with Excel’s results. Testing boundary cases—such as zero quantities, blank inputs, or the first and last date in a period—often reveals assumptions that are not obvious from a formula’s appearance.

Find and interpret formula errors

Choose Formulas > Formula Auditing > Error Checking to review detected errors and follow Excel’s prompts. The tool may identify inconsistent formulas or other common issues, but a warning is not proof that a result is wrong. For instance, an intentional formula that differs from the row above can be flagged, while a consistently copied formula can still contain the same mistake in every row.

Recognizing the error value narrows the investigation:

  • #DIV/0! usually means a formula divides by zero or by a blank cell treated as zero.
  • #N/A often indicates that a lookup did not find a match, although some formulas use it deliberately to mark unavailable data.
  • #VALUE! can indicate that an operation received the wrong kind of value, such as text where a number is expected.
  • #REF! means a reference is invalid, often because a referenced cell or range was deleted.
  • #NAME? can appear when Excel does not recognize text in a formula, such as a misspelled function or an unrecognized name.
  • #NUM! can indicate an invalid numeric argument or a calculation Excel cannot complete.

Follow the error to its source rather than hiding it immediately. Wrapping a calculation in IFERROR may make a worksheet look cleaner, but returning a blank or zero can conceal a broken lookup or an unexpected division problem. Choose that approach only when the alternate result is appropriate and users can still distinguish a genuine zero from a missing or failed calculation.

Show formulas and compare neighboring cells

To display formulas in worksheet cells instead of their calculated results, choose Formulas > Formula Auditing > Show Formulas. On Windows, Ctrl+` (the grave-accent key, often beside the number 1) toggles this view; the key location and shortcut behavior can vary with the keyboard layout. In Excel for Mac, the equivalent shortcut is Control+`; if it does not work with your keyboard layout, use the Show Formulas command on the Formulas tab. Widen columns if formulas appear as rows of hash marks or are cut off.

Showing formulas makes it easier to scan a block for a missing range, a hard-coded number, or a reference that points to an unexpected column. It is particularly useful for comparing repeated calculations. For example, a column of subtotals should generally follow the same pattern, with row references shifting as expected. Select a suspect formula and compare its text in the formula bar with the cells above and below; do not assume that matching displayed results mean the formulas match.

You can also select a range and choose Home > Find & Select > Go To Special > Formulas to select cells containing formulas. The Go To Special dialog can distinguish formula results by type, which may help in a mixed range. This selection identifies formulas, not their correctness. A useful review is to compare formula cells with nearby constants and confirm that inputs and calculated outputs are clearly distinguishable to the people who maintain the workbook.

Check references and calculation settings

References can change when a formula is copied. A relative reference such as B2 moves with the formula; an absolute reference such as $B$2 stays fixed; and a mixed reference fixes either the column or the row. When auditing a copied formula, check that the movement is intentional. A tax rate or conversion factor used in every row may need an absolute reference, while a quantity that changes by row usually needs a relative one.

Also verify that the formula points to the intended sheet and range, and that the range includes all relevant records. Structured table references and named ranges can make formulas easier to read, but you may need to inspect the table or name definition to see exactly what is included. If a result appears stale, check the workbook’s calculation setting under Formulas > Calculation Options. In Manual calculation mode, formulas may not update automatically after inputs change; calculate the workbook only after considering the time and effects of recalculating a large model.

For a quick consistency review, compare the first, middle, and last formulas in a repeated section, then inspect any row that differs. A difference may be legitimate—for example, a final row may use a different rule—but it deserves an explanation. Keep a record of intentional exceptions so another person does not “correct” a deliberate formula later.

Limits and practical cautions

Formula auditing can clarify a workbook’s mechanics, but it cannot validate the underlying assumptions, source data, or intended policy. A formula can be syntactically valid, have no Excel error, and still calculate the wrong thing. Confirm the expected result independently where possible, especially for totals, date boundaries, and formulas that influence decisions. In shared workbooks, ask the owner before changing unfamiliar formulas or external links.

The full Formula Auditing toolset is available in Excel desktop for Microsoft 365 on Windows. Excel for Mac provides many corresponding commands, though locations and shortcuts can differ. Excel for the web supports common formula editing and review tasks, but desktop-only or differently presented auditing features may not be available there. If a command is missing, open the workbook in the desktop app and check the Formulas tab. Avoid treating a workbook’s formula display as a security feature: hiding formulas or protecting a sheet is not a reliable way to keep sensitive logic confidential.

Frequently Asked Questions

How do I trace where an Excel formula gets its value?
Select the formula cell and choose Formulas > Formula Auditing > Trace Precedents. Excel draws arrows to referenced cells. Select an input and repeat the command to follow a chain of calculations.

How can I see which formulas use a particular cell?
Select that cell and choose Formulas > Formula Auditing > Trace Dependents. The arrows show formulas that reference it. Repeat on downstream cells if you need to follow the dependency further.

Can Excel show me how a formula is calculated?
Yes. Select the cell and choose Formulas > Formula Auditing > Evaluate Formula. Advance through the expression to inspect intermediate values and identify where the result diverges from what you expect.

Why does Trace Precedents show no arrows?
The selected cell may contain a constant rather than a formula, or its references may not be displayed as a simple direct relationship. Confirm the formula in the formula bar, check for named ranges or external references, and try selecting a different cell in the calculation chain.

Does Error Checking prove that a formula is correct?
No. It can flag common errors and inconsistencies, but it cannot determine whether a formula matches the intended rule. Review the references, compare related formulas, and test the result against known inputs.

Related

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.