Your formula returns the wrong result and you can’t see why. Or it shows #REF! and you don’t know which reference is wrong. Excel has powerful formula auditing tools that few people know about — tools that make troubleshooting 10 times faster.

This guide shows you how to use Excel’s built-in formula auditing features like a professional. You will learn to trace precedents, evaluate formulas step by step, and find errors that are impossible to spot any other way.

For general troubleshooting, see the Excel troubleshooting guide. For common problems, read common Excel problems.

The Formula Auditing Tools: Overview

All the tools are found on the Formulas tab in the “Formula Auditing” group.

The toolbox

Tool Function When to use it
Trace Precedents Shows which cells the formula uses Check that the right cells are referenced
Trace Dependents Shows which formulas use the cell See the impact of changes
Remove Arrows Clears all trace arrows Clean up the view
Show Formulas Shows formulas instead of results Overview of all formulas
Error Checking Finds potential errors Automatic review
Evaluate Formula Step-by-step evaluation Understand complex formulas
Watch Window Monitor cells in real time Follow values while you edit

Keyboard shortcuts: – Ctrl+ö (Swedish Excel): Show formulas – Ctrl+` (English Excel): Show formulas – F2: Edit the cell and show colour-coded references

Tool 1: Trace Precedents

What it does: Draws blue arrows from the cell to all the cells it refers to.

How to use it

  1. Select the cell with the formula
  2. Formulas → Trace Precedents
  3. Blue arrows show which cells the formula uses
  4. Click several times to trace several levels

Example:

C1: =A1+B1

Trace Precedents → arrows from C1 to A1 and B1.

Practical use

Scenario: Verifying VLOOKUP

E5: =LETARAD(D5;A1:B100;2;0)

Trace Precedents shows: – Arrow from E5 to D5 (lookup value) – Rectangle around A1:B100 (lookup table)

Problem: Does the arrow point to D4 instead of D5? → The reference is wrong; the formula was copied without adjustment.

Interpreting trace arrows

Arrow colour Meaning
Blue Normal precedent
Red The error is caused by this reference
Black dotted Reference to another sheet/workbook

Remove Arrows

Formulas → Remove Arrows → Choose: – Remove Precedent Arrows — Precedents only – Remove Dependent Arrows — Dependents only – Remove All Arrows — Everything

Tool 2: Trace Dependents

What it does: Draws blue arrows from the cell to all the formulas that use it.

How to use it

  1. Select the cell
  2. Formulas → Trace Dependents
  3. Blue arrows show which formulas depend on the cell

Example:

A1: 100
B1: =A1*1,25
C1: =A1+B1

Select A1, trace dependents → arrows to B1 and C1.

Practical use

Scenario: Understanding the impact of a change

You are about to change a value in A1. Which formulas are affected?

Trace Dependents → you see all the dependent cells.

Scenario: Finding the cause of a circular reference

If you have a circular reference: 1. Go to the cell Excel points to 2. Trace dependents 3. Follow the arrows 4. When an arrow leads back to the original cell, you have found the circle

Tool 3: Show Formulas (Ctrl+ö)

What it does: Shows formulas instead of results in all cells.

How to use it

  1. Press Ctrl+ö (or Ctrl+` in English Excel)
  2. All formulas are displayed as text
  3. Column widths adjust automatically to fit the formulas
  4. Press Ctrl+ö again to return to the results view

Practical use

Scenario: Overview of the whole model

When you take over someone else’s file: 1. Press Ctrl+ö 2. You see all the formulas at once 3. It is easy to identify patterns, errors or illogical references

Scenario: Documentation

To create documentation of formulas: 1. Show formulas 2. Take a screenshot or print

Scenario: Finding hardcoded values

In formula view it is easy to see what is a formula and what is a hardcoded value.

Tool 4: Evaluate Formula

What it does: Step-by-step evaluation of a complex formula.

How to use it

  1. Select the cell with the complex formula
  2. Formulas → Evaluate Formula
  3. A dialog opens with the formula
  4. Click “Evaluate” repeatedly
  5. Excel shows the result of each sub-calculation

Example:

=OM(OCH(A1>10;B1<5);SUMMA(C1:C10);0)

Step-by-step evaluation:

Steg 1: =OM(OCH(100>10;3<5);SUMMA(C1:C10);0)
        (A1 och B1 ersätts med värden)

Steg 2: =OM(OCH(SANT;SANT);SUMMA(C1:C10);0)
        (Jämförelserna utvärderas)

Steg 3: =OM(SANT;SUMMA(C1:C10);0)
        (OCH utvärderas)

Steg 4: =OM(SANT;250;0)
        (SUMMA utvärderas)

Steg 5: =250
        (OM utvärderas)

Practical use

Scenario: Formula gives an unexpected result

You can’t see why. Evaluate Formula shows exactly at which step it goes wrong.

Scenario: Learning complex formulas

When you need to understand someone else’s complex formula: evaluate it step by step to see the logic.

Tool 5: Error Checking

What it does: Automatically finds potential errors in the workbook.

How to use it

  1. Formulas → Error Checking
  2. Excel goes through the workbook
  3. Stops at each potential error
  4. You can:
    • Help on this error — Read the explanation
    • Show Calculation Steps — Opens Evaluate Formula
    • Ignore Error — Flags it as OK
    • Edit in Formula Bar — Fix it directly

Common errors that are found

  • Number stored as text — A number in a cell formatted as text
  • Formula omits adjacent cells — SUM(A1:A9) but A10 also has data
  • Inconsistent formula — The formula differs from the surrounding cells
  • Error messages — #REF!, #N/A!, #DIV/0!, etc.
  • Cells that refer to empty cells

Settings

File → Options → Formulas → “Error Checking” section

Here you can enable/disable specific error types.

Tool 6: Watch Window

What it does: Lets you monitor specific cells while you work in other parts of the workbook.

How to use it

  1. Formulas → Watch Window
  2. Click “Add Watch…”
  3. Select the cells to monitor
  4. Click OK

A window now appears showing: – The cell’s name – Value – Formula – Sheet

Benefits: – See cells from different sheets at the same time – Values update in real time as you edit – Click a cell in the list to jump to it

Practical use

Scenario: Complex model with many sheets

You have input on Sheet1, calculations on Sheet2-5, and the final result on Sheet6.

Add the final results to the Watch Window. Now you see them update in real time when you change the input on Sheet1.

A practical workflow for formula troubleshooting

Step 1: Reproduce the problem

Make the change that produces the wrong result. Document exactly what you did.

Step 2: Isolate the problem

Use the Watch Window to monitor the final result. Change one input at a time to see when the result goes wrong.

Step 3: Trace the data flow

From the problem cell: 1. Trace precedents 2. Do the arrows go to the right cells? 3. Double-check each precedent 4. Trace their precedents too (several levels)

Step 4: Evaluate the logic

If the references are right but the result is wrong: 1. Evaluate Formula 2. At which step does it go wrong? 3. Fix the logic at that step

Step 5: Verify the solution

After the fix: 1. Remove all arrows (clean up the view) 2. Trace precedents again 3. Confirm that everything points the right way 4. Test with different input values

Advanced tips

Colour coding when editing (F2)

Press F2 to edit a cell with a formula. Excel colour-codes the references:

=A1+B1

A1 turns blue, and cell A1 is marked with a blue border. B1 turns red, and cell B1 is marked with a red border.

Use: Makes it easy to see which cells are referenced. Drag the borders to change references visually.

Search for references

Find all formulas that refer to a specific cell:

  1. Ctrl+F (Find)
  2. Type the cell reference: A1
  3. Options → Look in: Formulas
  4. Find All

Excel finds all formulas containing A1.

Use named ranges

Instead of:

=LETARAD(E5;$A$1:$B$100;2;0)

Name the range “PriceList”:

=LETARAD(E5;PrisLista;2;0)

Benefits: – Easier to read – Clearer when troubleshooting – Lower risk of errors when copying

Common mistakes in formula auditing

1. Forgetting to remove arrows

If you have traced precedents in several cells, it quickly becomes messy. Remove all arrows and start again.

2. Blindly trusting Error Checking

Error Checking only finds certain types of errors. It does not find logic errors (a wrong formula that technically works).

3. Editing formulas while arrows are displayed

The arrows are not updated automatically. Remove the arrows and trace again after making changes.

4. Not using the Watch Window

If you work with large models with many sheets, the Watch Window saves a lot of time.

When you need professional help

Contact Excel Department if you have:

  • Complex formulas you don’t understand despite troubleshooting
  • Large models with circular dependencies
  • Performance problems caused by formulas
  • A need for restructuring to make maintenance easier

We offer: – Formula auditing and documentation – Rewriting complex formulas into simpler logic – Optimisation for faster calculation – Training in formula auditing

Call 010-264 20 20 or email info@exceldepartment.se.

Summary: Your formula auditing checklist

For every formula problem:

Related content

Excel Department Slottsbacken 8, Stockholm Phone: +46-10-264-20-20 / 010-264 20 20 Email: info@exceldepartment.se

Need help with this? Our Excel consultants will help you – the first consultation is free of charge.

Book a free call or call 010-264 20 20.