Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 93 ยท Formulas Fundamentals

Auditing and Debugging Formulas

Learn to debug Excel formulas: trace precedents and dependents, Show Formulas, Watch Window, F9 and Evaluate Formula, circular references and calculation modes.

A formula that returns the wrong number is harder to deal with than one that returns an error, because nothing looks broken. Auditing is the habit of checking how a result was produced: which cells fed it, which parts of the formula contribute what, and whether the workbook is even recalculating. Excel has a good set of tools for this, and they turn "I cannot see why this is wrong" into a few focused clicks.

This lesson covers tracing precedents and dependents, showing all formulas at once, the Watch Window, evaluating a formula step by step and in pieces with F9, finding circular references, and Excel's calculation modes. These are Excel features, so the instructions are read-only text. The live editors are stand-ins: a formula engine that evaluates fragments the way F9 does, and a dependency graph that shows what Excel tracks behind the scenes when it decides what to recalculate.

Four questions when a number looks wrong

What feeds it? Trace precedents.
What uses it? Trace dependents.
Which part is off? Evaluate Formula and F9.
Is it recalculating? Check calculation mode.

Debug one question at a time.

1 / 2

Precedents and dependents

Two terms describe how cells relate:

  • The precedents of a cell are the cells its formula uses. In B3: =B1+B2, the cells B1 and B2 are precedents of B3.
  • The dependents of a cell are the cells whose formulas use it. B3 is a dependent of B1.

On the Formulas tab, in the Formula Auditing group, Trace Precedents draws blue arrows from the cells a formula uses to the formula cell, and Trace Dependents draws arrows from a cell to the formulas that use it. Click either button again to follow the chain one more level back or forward. Remove Arrows clears them. If an arrow points to a small worksheet icon, the precedent is on another sheet; double-click the arrow to jump there.

Two keyboard shortcuts select the same cells directly: Ctrl+[ selects the direct precedents of the active cell and jumps to them, and Ctrl+] does the same for dependents. Add Shift for all levels. Handy before you delete or overwrite a cell: trace its dependents first and you will know what will change.

Show Formulas and consistency checks

Normally you see results, not formulas. Press Ctrl and the backtick key (`, under Esc on many keyboards) or click Show Formulas on the Formulas tab to display the formula in every cell at once. Columns widen, and you can scan a block for a formula that looks different from its neighbors, such as a number typed over the middle of a column of formulas. Press the same shortcut again to go back.

Other quick checks:

  • Home โ†’ Find & Select โ†’ Go To Special โ†’ Formulas (or Constants) selects every formula (or every typed value). Pick Row differences (Ctrl+\) after selecting a row, to highlight cells whose formulas do not match the first one.
  • The green error-check triangle flags "inconsistent formula" and "formula omits adjacent cells" for you.
  • Switching to R1C1 style, covered in Cell References: Relative, Absolute, Mixed, makes inconsistent formulas look different.

Inquire and Spreadsheet Compare

Some Microsoft 365 and Office Professional Plus editions on Windows include the Inquire add-in (File > Options > Add-ins > COM Add-ins > Inquire). Once it is on, a new Inquire tab offers a Workbook Analysis report (counts of formulas, hard-coded numbers, errors, hidden sheets, external links and VBA), Workbook Relationship and Worksheet Relationship diagrams that draw how files and sheets depend on each other, Cell Relationship (a diagram of one cell's precedents and dependents, going further than trace arrows), Clean Excess Cell Formatting (shrinks files slowed by formatting that reaches the bottom of the sheet) and Compare Files, which opens the separate Spreadsheet Compare tool to list every changed formula, value and format between two versions. The tab is missing in other editions and in Excel for Mac and the web; there you rely on the Formulas-tab tools above and on version history.

The Watch Window

Sometimes the cell you are editing and the cell you care about are far apart, or on different sheets. The Watch Window (Formulas โ†’ Watch Window โ†’ Add Watch) is a small floating panel that lists chosen cells with their sheet, name, current value and formula. Add the total you care about, then change inputs elsewhere and see the effect without scrolling.

Evaluating a formula in pieces

Long formulas are easiest to debug by examining their parts.

Evaluate Formula (Formulas โ†’ Evaluate Formula) opens a dialog that shows the formula with one part underlined. Click Evaluate and Excel replaces the underlined part with its value, then underlines the next part. Step In goes inside a nested reference, Step Out returns. You see every intermediate value until the final result, so the step where something unexpected appears is where the problem is.

F9 in the formula bar gives you the same insight with more control. Click into the formula bar, select a piece of the formula (for example SUM(B2:B4)>100), and press F9: the selected text is replaced by its current value. When you have seen what you needed, press Esc to cancel and restore the formula.

If you press Enter after F9, the calculated value replaces the formula part permanently. Always press Esc to back out. If you pressed Enter by mistake, use Ctrl+Z immediately.

Doing it in the stand-in

The editor below does the same thing with the stand-in engine. It evaluates a whole formula and then each piece of it, exactly like selecting parts in the formula bar and pressing F9. The second part hunts for an error: it evaluates each fragment until the guilty one shows up.

JS F9: evaluate the pieces of a formula

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Circular references

A circular reference happens when a formula refers to its own cell, directly or through other cells. The classic accident is a total that includes itself: typing =SUM(B1:B5) into B5. Excel cannot calculate this, because the answer depends on itself.

When you enter a circular formula, Excel shows a warning dialog. After you dismiss it, the cell typically shows 0 and the status bar shows "Circular References" with the address of one involved cell. To find them, open Formulas โ†’ Error Checking โ†’ Circular References, which lists the cells.

To fix one, trace the precedents of the cell and find the reference that points back. Usually you simply extend or move a range so it does not contain the formula cell, for example change =SUM(B1:B5) in B5 to =SUM(B1:B4).

Excel does have an option to allow some intentional circular models: File โ†’ Options โ†’ Formulas โ†’ Enable iterative calculation, with a maximum number of iterations (100 by default) and a maximum change (0.001 by default). Excel then recalculates the loop repeatedly until the numbers settle. This is occasionally used for things like interest on an average balance, but it can hide mistakes, so leave it off unless a model really needs it.

Calculation modes

Excel normally recalculates a formula as soon as something it depends on changes. This is Automatic mode. Under Formulas โ†’ Calculation Options you can choose:

Mode What it does
Automatic Recalculates dependents on every change (the default).
Automatic Except for Data Tables Same, but skips what-if data tables until you ask.
Manual Recalculates only when you press F9 (all open workbooks), Shift+F9 (the active sheet) or Calculate Now.

Manual mode is meant for huge workbooks that are slow to recalculate. The danger is forgetting you are in it: numbers go stale, and the status bar shows "Calculate" as the hint. If a total does not move when you change an input, check this setting first. Ctrl+Alt+F9 forces a full recalculation of everything, a useful last resort if you suspect a stale value.

One more fact helps explain speed: Excel keeps a dependency graph and, after an edit, recalculates only the cells that depend on the edit (in an order where inputs come before the formulas that use them). Some functions, such as NOW, TODAY, RAND and OFFSET, are volatile: they are recalculated on every change, along with everything that depends on them.

The dependency graph, as code

The stand-in below builds that graph from formula text. It lists precedents and dependents, shows which cells need recalculating after an edit, simulates manual mode with a "dirty" list, and detects circular references by walking the graph. It does not evaluate numbers; it models what Excel tracks.

JS A dependency graph with cycle detection

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice that editing A3 touches only B2, B3 and B4; B1 does not depend on A3 and is left alone. That is why Excel stays fast on big sheets, and why a volatile function in a key place can be expensive.

A debugging routine that works

  1. Reproduce. Which cell is wrong, and what should it be? Work out the expected number by hand for a small case.
  2. Check calculation mode. Is the status bar showing "Calculate"? Press F9.
  3. Trace precedents of the wrong cell. Are all the inputs what you expect, with no numbers stored as text and no hidden rows or columns?
  4. Evaluate the formula in pieces with F9 or Evaluate Formula to find the first part that differs from your expectation.
  5. Check consistency with Show Formulas or R1C1, and Ctrl+\ row differences.
  6. Fix at the source rather than patching the output with an extra correction.

For errors in particular, see Formula Errors and How to Fix Them. When you are ready to write sturdier formulas in the first place, Percentages, Rounding and Number Precision explains why some results are off by a tiny amount, and Handling Errors and Blanks in Formulas shows defensive patterns.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In B3: =B1+B2, how are B1 and B2 related to B3?

    Precedents are the cells a formula uses. B3 uses B1 and B2, so they are its precedents, and B3 is their dependent.
  2. 2. You select part of a formula in the formula bar and press F9. How do you restore the formula afterwards?

    Esc cancels the edit and puts the original formula back. Pressing Enter would permanently replace that part of the formula with its value.
  3. 3. You type =SUM(B1:B5) into B5. What do you get?

    The range includes the formula cell itself, so its value depends on itself. Excel warns about the circular reference.
  4. 4. An input changes but a total does not. What should you check first?

    In Manual mode Excel does not recalculate until you press F9. The status bar shows Calculate when results may be stale.
  5. 5. Which of these is a volatile function that recalculates on every change?

    NOW (like TODAY, RAND and OFFSET) is recalculated on every recalculation, along with every formula that depends on it.

Summary

  • Precedents feed a formula; dependents use it. Trace both on the Formulas tab, or jump with Ctrl+[ and Ctrl+].
  • Show Formulas (Ctrl + backtick), Go To Special and R1C1 style help spot inconsistent formulas; the Watch Window tracks key cells while you edit elsewhere.
  • Evaluate Formula steps through a formula; F9 on a selected part shows its value (press Esc, not Enter, to restore).
  • A circular reference means a formula depends on itself; find it under Formulas โ†’ Error Checking, and fix the range. Iterative calculation exists but is rarely a good idea.
  • Calculation mode can be Automatic or Manual; a stale total often means Manual mode. Volatile functions recalculate on every change.
  • Excel recalculates only the cells that depend on an edit, using a dependency graph.

Next: Percentages, Rounding and Number Precision.