Expires in:
In this article you will learn how to use three essential Excel formula auditing tools: trace precedents, trace dependents, and show formulas. These tools help you trace calculation chains, find the root cause of errors, and quickly distinguish formulas from hard coded values. This guide is for anyone who builds or maintains Excel models, from beginners who want to debug confidently to intermediate users looking to speed up troubleshooting.
What You'll Learn / Key Take-Aways
How trace precedents identifies the upstream cells that affect a selected formula cell.
How trace dependents finds downstream cells that are affected by an input cell.
How Show Formulas reveals which cells contain formulas versus hard coded values.
Useful keyboard shortcuts (Ctrl+G Go To Special, Ctrl+[ and Ctrl+]) to speed up auditing.
Practical workflow to diagnose formula chains and follow errors to their root cause.
Why formula auditing matters
When a workbook grows beyond a few sheets, tracing who influences what becomes hard. Errors propagate. A single mistyped input can turn a whole chain red. Formula auditing tools are built into Excel to make this tracing visual and fast. Instead of clicking cell by cell, you can reveal upstream inputs, downstream impacts, and every formula at a glance. That saves time and reduces costly mistakes.
The three core auditing tools
Show Formulas
What it does
Show Formulas temporarily displays the formulas in each cell instead of their computed results. This makes it obvious which cells are formulas and which are static values.
When to use it
When you inherit a workbook and want to quickly spot where formulas live.
When you suspect a calculation is hard coded and should be dynamic.
How to use it
Go to the Formulas tab and click Show Formulas in the Formula Auditing group.
Toggle it off to return to normal view.
Quick tip: To select only the cells that contain formulas, press Ctrl+G, click Special, choose Formulas, and press OK. That selects all formula cells so you can inspect, format, or copy them.
Trace Precedents
What it does
Trace Precedents draws arrows from the cells that feed into the selected cell. In other words, it shows you the upstream inputs that determine the selected cell's value.
Why this matters
If a result is wrong, knowing the upstream cells that influence it is the first step to diagnosing why. Trace Precedents makes that chain visible.
How to use it
Select a formula cell (for example, Cash to Close).
On the Formulas tab, click Trace Precedents.
Excel draws arrows from any cells that affect the active cell.
Keyboard shortcut: Press Ctrl+[ to select the precedent cells shown by the arrows. Pressing Ctrl+[ repeatedly will step further upstream (selecting precedents of precedents).
Trace Dependents
What it does
Trace Dependents draws arrows from the selected cell to any cells that depend on it. It shows the downstream impact of changing an input.
Why this matters
Before editing an input, you want to know what will be affected. If changing a percentage affects a cascade of cells, you need to be sure.
How to use it
Select an input cell (for example, Down Payment Percentage).
Click Trace Dependents on the Formulas tab.
Excel will show arrows to any cells that reference that input.
Keyboard shortcut: Ctrl+] will select the dependent cells. Pressing Ctrl+] repeatedly will follow the chain of dependents downstream.
Practical walkthrough: find the root cause of an error
Start at a visible error. Select the cell showing an error value. If nothing is upstream, Trace Dependents may show no dependents. Instead, use Trace Precedents to find inputs.
Use Trace Precedents on the error cell to reveal which upstream cells influence it.
If those upstream cells are formulas, trace their precedents as well until you reach inputs (hard coded cells users edit).
If you suspect a specific input, select it and use Trace Dependents to see where it flows. That helps you confirm whether it is responsible for the error.
Example scenario
In the property calculator example we used, entering the letter O instead of zero in the down payment percentage converted a numeric input into text. Trace Dependents from that input revealed a chain: Down Payment Percentage -> Loan Amount -> Mortgage Cost -> Monthly Expenses. The chain turned red where the error started and propagated downstream. Fixing the input fixed the whole chain.
Useful keyboard shortcuts and quick commands
Ctrl+G then Special then Formulas: select all formula cells.
Trace Precedents: Formulas tab > Trace Precedents or use Ctrl+[ to select precedents.
Trace Dependents: Formulas tab > Trace Dependents or use Ctrl+] to select dependents.
Remove Arrows: Formulas tab > Remove Arrows to clear the visual markers.
Additional Tips, Pitfalls to Avoid & Pro Advice
If you see arrows pointing to a gray dotted box, that means precedents or dependents are on another sheet or workbook. Follow the trace manually if needed.
Errors propagate. An error in an upstream cell will typically create errors in any dependent cells until the broken link is fixed. Trace the chain back to the first error and fix that cell.
Show Formulas can stretch the sheet layout because formulas are usually longer than their results. Toggle it off when you are done.
Use Go To Special (Ctrl+G) to select formula cells, then apply a temporary fill color to review them visually across the workbook.
Be careful when editing inputs. If an input feeds many calculations, consider protecting or documenting it so other users know its role.
FAQ
Q: How do I find which cells affect this formula in Excel?
A: Select the formula cell and use Trace Precedents on the Formulas tab. Excel draws arrows from the cells that feed into the selected cell. Use Ctrl+[ to select those precedent cells.
Q: How do I see which cells will change when I edit an input?
A: Select the input cell and click Trace Dependents on the Formulas tab. Excel draws arrows to any cells that reference that input. Use Ctrl+] to jump through dependent layers.
Q: How can I quickly show all formulas in a worksheet?
A: Use Show Formulas on the Formulas tab. This toggles display so formulas appear as text. To select only the formula cells, press Ctrl+G, choose Special, and select Formulas.
Q: Why are my trace arrows showing in red?
A: Excel uses red arrows to indicate error cells in the chain. If an upstream cell contains an error, subsequent dependents may also show red arrows because the error propagates.
Q: Do trace precedents and dependents work across worksheets or workbooks?
A: Yes. If precedents or dependents are on another sheet or workbook, Excel shows a dotted box. You will need to navigate to the referenced sheet or open the linked workbook to inspect those cells.
Q: Can I automate auditing across many sheets?
A: Excel does not provide a full built-in automation for multi-sheet auditing, but you can use macros to loop through sheets and record precedents or dependents. For most use cases, manual tracing combined with Go To Special is sufficient.
Summary of Key Points / Take-Home Messages
Use Show Formulas to quickly identify which cells contain formulas versus hard coded values.
Use Trace Precedents to reveal upstream inputs that determine a formula result.
Use Trace Dependents to follow the downstream impact of an input value.
Keyboard shortcuts (Ctrl+G, Ctrl+[ and Ctrl+]) speed up the auditing workflow.
Trace the first error you see back upstream to find the root cause, then fix that cell to clear the whole chain.
