From the Not Just Numbers blog:

Before I get into today’s post, just a quick reminder for those of you in London about the Twenty Principles event I mentioned in my last post.

I believe that there are still some places available for Wednesday evening, and it would be great to see you there.

I am currently working on a large project trawling through some incredibly complex spreadsheets built by someone else, and I thought that I would share with you a simple, but very valuable Excel feature that I am using a lot on this project.

Before I can make decisions about any particular cell in the spreadsheet, I need to know how it is linked to other cells.

These links go in both directions. If the cell contains a formula, I might want to know what other cells this cell is dependent on (referring to the formula will help in this case).

The contents of the cell will be of no help with the other direction, and this is the one I need to know most often, i.e. what cells are dependent on this one. This is crucial, because I can’t delete it or change it, until I know if there are any implications, and if so, what they are.

Fortunately Excel has a tool for both of these.

On the Formulas ribbon, there is a section called Formula Auditing which includes, among other things, the following three buttons:

  • Trace Precedents
  • Trace Dependents
  • Remove Arrows
If you select a cell with a formula in it, and click Trace Precedents, a number of blue arrows will appear, pointing from the cells that are referred to in the formula to the cell itself. If the formula refers to cells on another sheet, a broken arrow will come from a small box. In this case you can double-click on the arrow itself, and the references on other sheets will be listed. You can even double click on a reference in the list to go to that cell.
Even more useful is the Trace Dependents button. This works in exactly the same way, but the arrows go from your cell to the cells whose results depend on that cell. Where the cell is referenced from other sheets, the same approach (with the little boxes and broken arrows is used).
Finally, the last button simply removes the arrows from the screen.
Have a play with this. It can be very useful when you are trying to understand how a particular spreadsheet works.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.