Learned that figuring out Excel formula dependencies has been there since at least Excel 2000 (:
Posted by jpluimers on 2022/03/01
Sometimes, you figure out Excel functionality you have never needed before, but has been there for decades.
A while ago, I had a very complex with formulas referencing full columns back and forth when suddenly I got into something strange: when reloading the spreadsheet, values would not appear because of recursion errors. Before saving this was fine, so it was hard to track back where I want wrong.
So I was happy to find out that Excel has two cool features for this:
- Trace Precedents
- Trace Dependents
Heck, looking at the icons I had a feel these features had been there for a long time. Boy, was I surprised to find them in [Wayback] Excel 2000 – Student Edition – Complete (a great book by the way), as you can see in this picture:
Excel 2000 – Student Edition – Complete – Trace precedents, dependents, error
As others can explain this feature so much better than I can, here are some links:
- Excel 2007 and up (2012, 2016 and 365)
- [Wayback] Display the relationships between formulas and cells – Excel
When checking formulas, use the Trace Precedents and Trace Dependents commands to display the relationships between these cells and formulas.
…
- Precedent cells — cells that are referred to by a formula in another cell. For example, if cell D10 contains the formula =B5, then cell B5 is a precedent to cell D10.
-
Dependent cells — these cells contain formulas that refer to other cells. For example, if cell D10 contains the formula =B5, cell D10 is a dependent of cell B5.
…
you can use the Trace Precedents and Trace Dependents commands to graphically display and trace the relationships between these cells and formulas with tracer arrows, as shown in this figure.
- [Wayback] Trace Precedents and Dependents in Excel | Smart Office
Excel offers us some useful tools for auditing a formula, in order to understand what we have done wrong in the formula, by tracking down the relationships between the cells of the formula in a spreadsheet…
- [Wayback] Trace Precedents in Excel: Find Formulas, Functions and Cells Connected to a Cell | Pryor Learning Solutions
- [Wayback] Display the relationships between formulas and cells – Excel
- Excel 2003: [Wayback] Tracing Formula Precedents – MS-Excel Tutorial
You use the Trace Precedents button on the Formula Auditing toolbar to trace all the generations of cells that contribute to the formula in the selected cell(something like tracing all the ancestors in your family tree).
- Excel 2000: [Wayback] Excel 2000 – Student Edition – Complete
–jeroen
The Wiert Corner - irregular stream of stuff 
Leave a Reply