How to Audit Formulas in Excel: Trace Precedents, Dependents, and Error Sources

A margin number looks wrong in a dashboard. A finance model you inherited from a colleague shows #REF! in six places. A nested IF returns a value nobody can explain. These are the moments formula auditing exists for — and Excel ships seven built-in tools for exactly this job, most of which beginners never learn to combine on the same workbook.

This tutorial walks through every tool in the Formulas → Formula Auditing group (Trace Precedents, Trace Dependents, Show Formulas, Error Checking, Evaluate Formula, Watch Window) plus two extras every auditor should know (the F9 inline-evaluation trick and the Inquire add-in). It ends with a six-step checklist for when you inherit a workbook you did not build — the single most common scenario where auditing actually pays for itself.

All tools below are available in Excel 365, 2021, 2019, and 2016 on Windows. On Mac, Evaluate Formula and the Inquire add-in are unavailable; everything else works. This tutorial pairs naturally with the Excel Formulas Guide hub — use that as the index for deeper formula-specific tutorials after you have the audit workflow down.

When formula auditing earns its keep

Auditing is a discipline, not a one-off rescue. The three scenarios where it is non-negotiable:

• The number looks wrong. A report figure does not match a sanity-check calculation. You need to trace the chain backwards from the wrong cell, not guess.
• You inherited the workbook. Somebody left, changed teams, or built a model six months ago that you now have to maintain. You have no idea which cells are hardcoded, which are formulas, or where the external links live.
• The model is about to be trusted for a real decision. A pricing change, a board deck, a compliance submission. Before the number leaves the workbook, someone has to have walked the logic end to end.

The Formula Auditing group: a tour

Go to Formulas → Formula Auditing. The group sits between Defined Names and Calculation. It contains, left to right: Trace Precedents, Trace Dependents, Remove Arrows, Show Formulas, Error Checking, and Evaluate Formula. Watch Window is in the same tab further right. These seven tools, used in the right order, answer every auditing question a beginner is likely to face.

Tool 1 — Trace Precedents (where does this value come from?)

Trace Precedents draws blue arrows from every cell that feeds INTO the selected cell. Click once and you see one level deep; click again and you see one more level back. It is the single most useful audit tool in Excel.

Shortcut: Alt + M + P (or Formulas → Trace Precedents). Remove Arrows: Alt + M + A + A.

Real-world scenario. A regional revenue total looks 12% too high. You select the total cell, press Alt + M + P, and see three arrows pointing into it — one from the Sheet1 summary, one from the Sheet2 summary, and a third coming from a dashed arrow with a small sheet icon (meaning the precedent is on another worksheet). Double-click the dashed arrow to open Go To, jump to the source, and discover a duplicated month that was pasted twice.

Beginner pitfall. Clicking Trace Precedents repeatedly on the same cell draws the next level each time, but the arrows never renumber. If you lose track of which level you are on, click Remove Arrows and start over. Do not try to read a fifth-level trace on a single screen — move to Evaluate Formula instead.

Tool 2 — Trace Dependents (what reads from this cell?)

Trace Dependents is the mirror image: it draws arrows FROM the selected cell TO every cell that depends on it. Use it before you edit or delete a cell, to measure the blast radius.

Shortcut: Alt + M + D. Keyboard alternative: Ctrl + ] jumps directly to the first dependent cell.

Real-world scenario. A colleague asks if it is safe to clear out a scratch column on the Assumptions sheet. You select the column header, press Alt + M + D, and see eleven arrows — some to adjacent cells, two to a dashboard sheet, one to a named range used in a pivot. "Scratch" was a lie. You now know to rename and isolate before deleting.

Beginner pitfall. Trace Dependents only finds references in open workbooks. If another workbook has a formula linked to this one and it is closed, the dependent arrow will not appear. Check File → Info → Edit Links to External Files before assuming a cell is safe to delete.

Tool 3 — Show Formulas (see the logic layer, not the numbers)

Show Formulas flips the whole sheet from displaying values to displaying the underlying formulas. It is the fastest way to spot hardcoded numbers inside formulas, inconsistent references, and cells that look like formulas but are actually typed values.

Shortcut: Ctrl + ` (the backtick, above the Tab key — same key as ~).

Real-world scenario. You inherit a budget and flip Show Formulas. Three cells in column F display plain numbers instead of =E5*1.08. Someone hardcoded the values after a quarterly review and forgot to restore the formula. Without Show Formulas you would not have noticed until next quarter's refresh produced a stale number.

Beginner pitfall. Show Formulas widens every column to fit the formula text. When you press Ctrl + ` to toggle back, Excel remembers and restores the original widths — do not manually resize while in formula view, or the original layout is gone.

Tool 4 — Error Checking (surface #REF!, #NAME?, #VALUE!, #DIV/0!)

Error Checking walks through every error cell in the sheet one at a time and explains the likely cause. It is the right first step when you open a broken workbook and do not know where to look.

How to use it. Formulas → Error Checking. A dialog opens on the first error. Click "Show Calculation Steps" to jump into Evaluate Formula, "Ignore Error" to skip, or "Edit in Formula Bar" to fix in place. Click Next / Previous to walk the whole sheet.

Error types to recognise.

• #REF! — the formula points at a cell or range that no longer exists (row deleted, sheet renamed).
• #NAME? — Excel cannot parse a name in the formula (typo, missing add-in, named range deleted).
• #VALUE! — a wrong type in an argument (text where a number is expected, a range where a single cell is expected).
• #DIV/0! — literal divide-by-zero or a blank divisor.
• #N/A — a lookup did not find the value (VLOOKUP/XLOOKUP/MATCH). Often intentional; wrap with IFERROR or IFNA.
• #SPILL! — a dynamic array (FILTER, UNIQUE, SORT) cannot expand because the destination cells are blocked. Clear the blocking cells.

Tool 5 — F9 and Evaluate Formula (the superpower most beginners skip)

This is the audit tool the top-ranking tutorials barely mention, and it is the most powerful one. It lets you step through a formula one sub-expression at a time.

The F9 trick (fast path). Double-click the cell to enter edit mode (or press F2). Inside the formula bar, highlight just ONE segment of the formula — for example, the VLOOKUP(A2, Table1, 3, FALSE) part of a larger nested IF. Press F9. Excel replaces the selection with its evaluated value. Now you can see what that sub-expression actually returns.

Critical. After you inspect the value, press Esc (not Enter). Enter commits the evaluated value and destroys the formula. If you press Enter by accident, Ctrl + Z restores the original.

The Evaluate Formula dialog (safe path). Formulas → Evaluate Formula. A dialog opens with the full formula and an underlined sub-expression. Click Evaluate to replace the underlined part with its value; click Step In to drill into a precedent cell; Step Out to return. It is the safer version of F9 — the formula is never at risk. Use it for formulas longer than two or three levels of nesting.

Real-world scenario. A commission calculation =IF(B2>Thresholds!$B$1, B2*Rates!$C$2, B2*Rates!$C$1) returns the wrong rate. F9 on the B2>Thresholds!$B$1 segment reveals TRUE when you expected FALSE. The threshold cell has 1000 where you expected 10000 — one missing zero, caught in ten seconds.

Tool 6 — Watch Window (monitor cells across sheets while you edit)

Watch Window pins the live values of chosen cells into a floating panel so you can see them update while you edit anywhere in the workbook.

How to use it. Formulas → Watch Window → Add Watch. Pick the cell(s) you want to monitor. The panel now shows workbook, sheet, cell, value, and formula for each watched cell, and updates in real time.

When it helps. You are editing an assumption on Sheet1 that feeds a final KPI on Sheet12. Rather than flipping tabs after every edit, pin the KPI cell to Watch Window and watch it update as you tune.

Tool 7 — Inquire add-in (enterprise audit)

The Inquire add-in ships with Excel 365 and 2021 on Windows (Professional Plus / Enterprise SKUs). Enable it via File → Options → Add-Ins → COM Add-Ins → Inquire.

Inquire runs a full Workbook Analysis report in one click: every formula, every external link, every hidden sheet, every inconsistency. It also produces a Cell Relationship Diagram — a visual graph of which cells feed which — far beyond what Trace Precedents can show. For a 20-sheet inherited model, this is a 10-minute audit that would take two days by hand.

Inherited workbook: a six-step audit checklist

If you just opened somebody else's workbook and need to trust it by end of day, run these in order. Each step is roughly 10–15 minutes on a typical 5-sheet model.

1. Show Formulas (Ctrl + `). Scan every sheet visually. Flag any cell where you expected a formula but see a hardcoded number, or where the formula shape is inconsistent with neighbours (a common sign of a manual override).
2. Error Checking. Walk every #REF!, #NAME?, #VALUE!, #DIV/0!. Fix or document each one. If a cell is intentionally #N/A, wrap with IFNA so it does not look like a bug to the next reviewer.
3. Name Manager (Ctrl + F3). Review every named range. Delete the ones that point to #REF!, rename the cryptic ones, check for duplicates that point to different ranges (a classic silent corruption).
4. Trace Precedents on every output cell. Identify the 5–10 cells that drive the final answer (totals, KPIs, the figures that will be quoted). Trace Precedents one level deep on each. Anything unexpected is either a bug or a hidden assumption worth documenting.
5. Find hardcoded numbers inside formulas. Ctrl + F, Options → Look in Formulas, search for common magic numbers (1.08, 0.15, 365, 12). Replace inline numbers with named cells so the assumption is visible and editable.
6. Inspect Document. File → Info → Check for Issues → Inspect Document. This reveals hidden sheets, hidden rows/columns, external links, embedded objects, and personal information you probably did not know was in there.

When to use which tool (decision table)

Worked example: the margin anomaly

The gross margin cell H12 on the dashboard shows 64% when everyone expects around 40%. Walk the audit:

1. Trace Precedents on H12. Two arrows: revenue (H10) and cost of goods (H11). H10 looks fine at 8.2M. H11 looks suspicious at 2.95M — last quarter it was 4.9M.
2. Trace Precedents on H11. One dashed arrow to another sheet — Costs. Double-click to jump.
3. On the Costs sheet, flip Show Formulas. H11 is =SUM(Costs!B2:B18). But the sheet now has rows through B24 — a new cost category ("Cloud Hosting", rows B19:B24) was added but the SUM range was never widened.
4. Fix: convert the cost table to an Excel Table (Ctrl + T) and reference the column by name: =SUM(CostsTable[Amount]). Now new rows auto-extend the sum. See the Excel Tables tutorial for why this is the durable fix.

The whole audit took four minutes. Without Trace Precedents it could have taken hours.

Troubleshooting

1. Trace Precedents arrows do not appear. The cell is either not a formula (it is a hardcoded value), or all its precedents are on closed workbooks. Select the cell and look at the formula bar to confirm it is a formula. If precedents are on a closed workbook, open that file first.
2. Dashed arrow ending in a small sheet icon. This is not a bug — it means the precedent lives on another worksheet or workbook. Double-click the dashed line to open the Go To dialog and jump to the real source.
3. F9 replaced my entire formula with a value. You pressed F9 without highlighting a sub-expression first, and then pressed Enter. Press Ctrl + Z immediately to undo. The rule: F9 evaluates whatever is currently selected inside the formula bar — if nothing is highlighted, that is the entire formula.
4. "Circular reference" warning at the bottom of Excel. A formula refers to its own cell either directly or through a chain. File → Options → Formulas → toggle Iterative Calculation OFF temporarily. Then Formulas → Error Checking → Circular References — Excel lists every offending cell. Break the loop; do not leave iterative calculation enabled unless the model genuinely requires it.
5. Evaluate Formula dialog is greyed out. You are on Mac (the dialog is Windows-only) or the cell does not contain a formula. On Mac, use the F9 inline trick instead, or install a third-party add-in like Macabacus.

Common mistakes

• Checking random cells instead of walking backwards from the wrong output.
• Assuming the formula is wrong when the assumption is wrong — a correctly-built formula on top of a bad input still returns the wrong answer.
• Fixing the display cell without Trace Dependents — the bad value may already have been copied into a filed report.
• Skipping Show Formulas on inherited workbooks. Hardcoded overrides inside formula columns are the single most common silent bug.
• Pressing Enter instead of Esc after F9. One keystroke away from overwriting a live formula with a static number.

Frequently asked questions

Where is the Formula Auditing group in Excel?

Formulas tab → Formula Auditing group. It contains Trace Precedents, Trace Dependents, Remove Arrows, Show Formulas, Error Checking, Evaluate Formula, and Watch Window. Available in Excel 365, 2021, 2019, and 2016 on Windows and Mac (Evaluate Formula and Inquire are Windows-only).

What is the difference between Trace Precedents and Trace Dependents?

Trace Precedents shows which cells feed INTO the selected cell (the inputs). Trace Dependents shows which cells READ FROM the selected cell (the outputs). Use precedents to find where a wrong value came from; use dependents to measure the blast radius of changing a cell.

How do I debug a long nested formula without rewriting it?

Put the cell in edit mode (F2), highlight one segment of the formula inside the formula bar, then press F9. Excel replaces the selection with its evaluated value so you can see which sub-expression is returning the wrong result. Press Escape (not Enter) to exit without overwriting the formula. For a step-by-step walkthrough use Formulas → Evaluate Formula instead.

Why do my trace arrows stop at the edge of the sheet?

When a precedent lives on a different worksheet or workbook, Excel shows a dashed arrow ending at a small worksheet icon instead of the real cell. Double-click that arrow to open the Go To dialog and jump to the actual source.

How do I audit an inherited workbook I did not build?

Run this order: (1) Show Formulas (Ctrl + `) to see the full logic layer, (2) Error Checking to surface #REF!/#NAME?/#VALUE!, (3) Name Manager to review named ranges, (4) Trace Precedents on every output cell one level deep, (5) look for hardcoded numbers inside formulas, (6) File → Info → Check for Issues → Inspect Document for hidden sheets and external links.

Related tutorials

• Excel Formulas Guide — the Excel cluster hub; start here if you want a curated index of every formula tutorial on this site.
• How to Fix Excel Formula Errors with Claude AI (Fast) — pair the built-in audit tools with Claude as a second pair of eyes on the hardest formulas.
• How to Review AI-Generated Excel Formulas Before You Trust Them — auditing is not just for human-written formulas; AI-generated ones need the same discipline.
• LET and LAMBDA in Excel: Turn Repeated Formulas Into Reusable Logic — write formulas that are auditable by design, not by rescue.
• Advanced Excel Formulas You Should Know — the formulas (XLOOKUP, FILTER, LAMBDA) that make audit trails shorter in the first place.
• Excel Tables: Structured References, Growth, and Cleaner Models — the structural fix behind the margin-anomaly example above.