Contents
The Need: Trace Dependents
Excel workbooks are complex. Multiple persons share and handle the same file. We keep mailing them to each other. Often the person who created the file has left the organization long back. There is never any documentation available about exactly how a file works.
Due to all this, it is almost impossible to remember which value is used in which formula. When you are about to change a value or add more data to existing block, it is absolutely necessary to ensure that all the related formulas are updated. If you forget to update the formula, the results shown there will be outdated and WRONG. This can hamper decisions and can have severe side effects.
What we need is a way to find out which formulas to update when any data changes or grows. Excel has provided this facility since 25 years. Unfortunately, very few people are even aware of it – leave alone using it!
The solution: Trace Dependents
Formulas depend upon other cells. For any cell Excel tells you these other cells which depend upon them (One cell at a time). Click on any cell containing a value (or formula) and choose Trace Dependents from the Formula tab.
Now Excel shows many blue arrows starting from that cell and pointing to various other places. Unless the dependent cell is nearby, you will not see the other end of the arrow. Just double click on it to go to the other end (DO NOT try to scroll – it could be very far away). Double click again to come back to the original cell.
References outside the current sheet are shown by ONE dotted line. Double clicking on that line opens the Go To dialog showing all non-current-sheet dependents. You can go to each one of them and update them. Unfortunately, when you choose one of them the dialog closes. To come back there is no blue arrow. In this case you can use the Trace Precedent option or remember the original cell by giving it a temporary name and use Go To feature.
Tracing dependents chain: DO you know how to do it?
It shows only first level dependents. But each of these dependents can have more dependents. How to trace them till we reach the last cell in each direction which does not have any further dependent?
Click on the starting cell. Now keep clicking on the Trace Dependents button repeatedly. It will keep tracing further and further dependents. But most probably, those dependent arrow will fan out in all directions – and will never be VISIBLE to you. So how do you know when to finish clicking (and when it has reached the last dependent in all directions)?
Good question. This is where Microsoft shines. Don’t just keep clicking – also listen. When it reaches the end point in all directions – it will BEEP. That is when you stop.
Smart – is it not?
Try this. Of course there is reverse of it also – Trace Precedents. This works FROM the formula to its ingredients. This is used for troubleshooting or auditing formulas.
Spreadsheet Analysis using Inquire
Excel 2013 has a extremely powerful tool to manage dependents and precedents. It can be used to create a graphical analysis of all dependencies. The tool is called Inquire. Read this article for details: Spreadsheet Analysis using Inquire – Relationship Diagrams
***
