Check Formulas in Excel: Trace Precedents and Evaluate

Trace Precedents in Excel draws arrows from the cells a formula uses, so you can see at a glance whether it reads the right range. Excel warns you when a formula breaks, but not when it quietly gives the wrong answer. This guide shows the auditing tools that catch those quiet errors: Trace Precedents, Trace Dependents, Show Formulas, Evaluate Formula, Error Checking and the Watch Window.

Where are the formula auditing tools?#

All of the tools in this guide live on the Formulas tab, in the Formula Auditing group in Excel for Windows. Excel for Mac has the same main buttons on its Formulas tab: Trace Precedents, Trace Dependents, Remove Arrows, Show Formulas and Error Checking.

Two words to know: a precedent is a cell that a formula reads. A dependent is a formula that reads the cell you selected.

How to use Trace Precedents in Excel#

Trace Precedents shows every cell or range that feeds the selected formula. Here is a small expense sheet with a problem:

1. Click B8, the total.
2. Click Formulas > Trace Precedents.
3. A blue arrow points into B8, and a blue box surrounds the range it adds.

The box covers B2:B6 only. Software was added in row 7 after the formula was written, so the total misses 89.99. The correct formula is =SUM(B2:B7), which gives 2,376.14.

Click Trace Precedents again to go one level further back. If B2 were itself a formula, a second set of arrows would show what B2 reads. A dashed black arrow with a small sheet icon means a precedent is on another sheet or in another workbook; double-click it to see where.

In Excel for Windows, Ctrl+[ selects the precedents instead of drawing arrows. That is handy for a formula that reads cells far away on the sheet.

How to use Trace Dependents before you delete a cell#

Trace Dependents works the other way round: select an input cell and Excel draws arrows to every formula that uses it.

Use it before you delete, move or clear a cell. If a tax rate in F1 is used by twenty price formulas, Trace Dependents shows all twenty arrows. Deleting F1 would turn every one of them into a #REF! error.

If Excel beeps or shows a message instead of arrows, no formula uses that cell. That is useful too: it tells you an input is not connected to anything. When you are done, click Remove Arrows.

Show Formulas: see every formula at once#

Show Formulas swaps every result for the formula behind it. Press Ctrl+** on Windows or **Control+ on a Mac (the ` key is to the left of 1), or click Formulas > Show Formulas. Press it again to switch back.

Scan down a column in this view. A column of formulas with one plain number in the middle usually means someone typed over a formula. That cell won't update when the inputs change.

This view also shows references at a glance. In a column of =B2*(1+$F$1), =B3*(1+$F$1), =B4*(1+$F$1), a row that says =B5*(1+F4) stands out. Cell references explained covers why relative references move when you copy them.

Evaluate Formula: step through a calculation#

Evaluate Formula calculates a formula one part at a time, so you can see exactly which part goes wrong.

Say C3 should add a 10% tax to a price, but it shows the same number as the price. Its formula is =B3*(1+F2): the reference to the rate moved down a row when the formula was copied.

1. Click C3 and choose Formulas > Evaluate Formula.
2. The box shows the formula with the next part to calculate underlined.
3. Click Evaluate. F2 becomes 0, because F2 is empty.
4. Keep clicking. (1+0) becomes 1, and the result is the price times 1.

The fix is to lock the rate cell as $F$1 and fill the formula down again: =B3*(1+$F$1). Evaluate Formula is in Excel for Windows. If your version doesn't show it, a Windows trick does the same job: in the formula bar, select one part of the formula and press F9 to see its value, then press Esc so the formula is not changed.

Error Checking and the Watch Window#

Error Checking looks for cells Excel thinks may be wrong and marks them with a small green triangle in the top-left corner. Common flags are a number stored as text, a formula that leaves out cells next to it, and a formula that doesn't match the ones around it. Click the cell, then the warning icon, to see the reason and a suggested fix.

A number stored as text is worth fixing right away, because SUM skips it. It often comes from imported data; Text to Columns can convert a whole column back to numbers in a few clicks.

If a formula refers to its own cell, Excel shows a circular reference warning. Formulas > Error Checking > Circular References lists the cell so you can fix the range.

The Watch Window (Formulas > Watch Window > Add Watch) keeps chosen cells in a small floating box. Add the grand total, then change inputs on another sheet and watch the total update without scrolling back.

A quick formula checklist#

Run through these checks before you send a workbook to anyone:

1. Totals: Trace Precedents on every total. Does the box cover every row?
2. Copied formulas: Show Formulas, then scan each column for a reference that breaks the pattern.
3. Fixed inputs: rates and targets should be locked with $, so every row points at the same cell.
4. Typed-over formulas: look for plain numbers in a column of formulas.
5. Green triangles: open each one and decide whether it is a real problem.
6. A sense check: do the department totals add up to the grand total?

Finance teams often ask about these habits, as the guide to Excel skills for finance jobs explains. "How would you check a spreadsheet someone else built?" is also a common question in our list of Excel interview questions.

Practise it#

Fix the total in the exercise on this page first. Then practise debugging SUM formulas, where you hunt a short range, a number stored as text and a circular reference, and practise relative and absolute references to fix copied formulas that point at the wrong cell.