Trace Precedents in Excel: How to Check Formulas
· 7 min read
To see which cells feed a formula, click the formula cell, go to the Formulas tab and click Trace Precedents in the Formula Auditing group. Excel draws blue arrows from every cell the formula uses, and a blue box around every range. Click it again to go one level further back. Click Remove Arrows when you are done.
Formulas > Formula Auditing > Trace PrecedentsThe same group has the other tools you need to check a sheet: Trace Dependents, Show Formulas, Error Checking and Evaluate Formula. This guide walks through each on a small example with a hidden mistake, the kind a quick glance misses.
The example: a summary with one wrong total
A regional summary, with totals in row 7 and the average order value (AOV) in D8. Column G has the corrected versions for comparison.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Manager | Orders | Revenue | Check | Result | |
| 2 | North | Priya | 42 | 5,460 | |||
| 3 | South | Tom | 35 | 3,990 | |||
| 4 | East | Ana | 51 | 7,140 | |||
| 5 | West | Marco | 38 | 4,560 | |||
| 6 | |||||||
| 7 | Total | 166 | 16,590 | Fixed total | 21,150 | ||
| 8 | AOV | 99.94 | Fixed AOV | 127.41 |
The formulas are =SUM(C2:C5) in C7, =SUM(D2:D4) in D7 and =D7/C7 in D8. Nothing shows an error. But the revenue total leaves out the West row. The real total is 21,150 and the real AOV is 127.41, not 99.94: those are G7 and G8.
Trace precedents: Excel arrows that show the inputs
Click D7 and choose Trace Precedents. Excel draws a blue box around D2:D4 with an arrow pointing to D7. The box stops one row short of the data, and West's 4,560 sits outside it. That is the bug, visible in one click. The sheet above does a simpler version of the same thing: click a formula cell and the cells it reads turn blue.
Now click D8 and choose Trace Precedents. Arrows come from D7 and C7. Click the button a second time and Excel adds the next level: the boxes around D2:D4 and C2:C5. The two boxes have different heights, which is a strong hint that one of them is wrong.
What the arrows mean:
- Blue arrows point from inputs to the formula. A range shows as a box with one arrow.
- Red arrows point from a cell that contains an error, when you use Trace Error.
- A black dashed arrow with a small sheet icon means the input is on another sheet or in another workbook. Double-click the dashed line to open a list of those references and jump to one.
Trace Dependents
Trace Dependents works the other way round. Click a cell and it shows which formulas use it. Click D5 (West's revenue) and choose Trace Dependents: no arrow appears, because no formula in the summary uses it (in the sheet above, only the fix in G7 does). A data cell that feeds nothing is often a sign that a total missed it.
Remove Arrows
Arrows stay on the sheet until you remove them. Remove Arrows clears all of them, and its drop-down lets you remove only precedent or only dependent arrows.
Show Formulas: see every formula at once
Show Formulas switches the sheet to display formulas instead of results. The shortcut is Ctrl+` (the grave accent key, usually left of 1). Press it again to switch back.
This is the fastest way to check a filled-down column. Formulas that should follow a pattern sit one under another, so a cell that breaks the pattern, such as a hard-typed number in the middle of a formula column, stands out.
Evaluate Formula: step through the calculation
For longer formulas, arrows are not enough. Evaluate Formula opens a window that calculates the formula one piece at a time.
Click D8 and choose Evaluate Formula. The window shows =D7/C7 with D7 underlined.
- Click Evaluate.
D7becomes 16590. - Click Evaluate again.
C7becomes 166. - Click Evaluate once more. The result appears, about 99.94.
When the underlined part is a reference to another formula, Step In shows that formula, and Step Out returns. Restart starts again from the top. This is how you find which part of a nested IF or a long lookup produces the surprise. To see every step of a formula at once instead of clicking through them, paste it into the free Excel formula checker.
Error Checking and common Excel formula errors
Error Checking jumps from one cell flagged with an error to the next and explains each. Its drop-down includes Trace Error, which draws red arrows back to the cell where an error started, and Circular References, which lists cells that refer to themselves.
The errors you will meet most:
| Error | Usual cause |
|---|---|
#DIV/0! | Dividing by zero or by an empty cell |
#N/A | A lookup did not find the value |
#NAME? | A misspelt function name, or a function your version does not have |
#REF! | The formula points at a cell that was deleted |
#VALUE! | Text where a number is expected, or ranges of different sizes |
#SPILL! | A formula that returns several cells is blocked by data in the way |
#NUM! | An impossible calculation, such as the square root of a negative number |
Error values are the easy case, because you can see them. Hiding them with IFERROR can make a sheet look clean while the cause stays. See IFERROR in Excel for when to hide an error and when to fix it. The harder case is a formula with no error and a wrong result, like the total above.
How to check formulas in Excel: a routine
- Do the parts add up to the whole? Region totals should match the grand total. If they do not, trace both.
- Trace every total. Do the boxes start on the first data row and end on the last one?
- Compare paired ranges. In SUMIF, SUMIFS, INDEX MATCH and lookups, the ranges you pair must start and end on the same rows. Two boxes that are offset by one row mean every value is matched with the wrong row.
- Look at the columns the arrows come from. If a revenue formula has no arrow from the status column, it is adding every status, including cancelled and refunded orders.
- Show Formulas on filled columns. Look for the cell that breaks the pattern.
- Evaluate anything nested. Step through it instead of guessing.
On Windows, Ctrl+[ selects the direct precedents of the active cell, which is handy when they are far away. You can also double-click a formula cell (or press F2) to see its references outlined in colour on the sheet.
Common mistakes
Trusting an AI's formula because it has no error. AI assistants hand back formulas that calculate fine but point at the wrong cells: a range that stops a row early, a sum range shifted one row down, or a revenue total that never looks at the status column. None of these show an error value. Trace Precedents is the quickest way to check. Click each formula the assistant wrote and ask: are these the cells I would have picked?
Checking only the first cell. A filled-down formula can be right in row 2 and wrong in row 20, when ranges were not locked. Trace the last cell too. Our absolute reference guide explains why.
Missing inputs on other sheets. A dashed arrow is easy to overlook. Follow it before you decide a formula is right.
Leaving arrows on the sheet. They are only a checking aid. Remove them before you send the file.
Fixing the symptom. If a total is too low, adding the missing number by hand (=SUM(D2:D4)+4560) makes the number right today and wrong tomorrow. Fix the range. See how to sum a column.
FAQ
Where is Trace Precedents in Excel?
On the Formulas tab, in the Formula Auditing group, next to Trace Dependents and Remove Arrows.
Is there a trace precedents Excel shortcut?
There is no single shortcut that draws the arrows. On Windows, Ctrl+[ selects the cells a formula uses directly, and Ctrl+] selects the formulas that use the active cell.
How do I check formulas in Excel?
Use Show Formulas (Ctrl+`) to see every formula, Trace Precedents to see what each one uses, and Evaluate Formula to step through long ones. Then check that the parts add up to the totals.
What does Evaluate Formula do?
It calculates a formula one step at a time, showing the value of each reference and each part. Use Step In to look inside a referenced formula.
Why does Trace Precedents show nothing?
The cell probably contains a typed value, not a formula, or the formula uses only constants. Select a formula cell and try again.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “The AI’s total looks low” →