Tracing the Path

Tracing the Path

Do you use complicated Excel formulas? You know, where a formula references cells that in turn reference other cells. It leads to a big chain reaction of values. One wrong or inadvertent change to a cell’s value and you could cause a multitude of errors in the values reported.

Ever find yourself wondering just where did that value really come from? I mean, which cells in the worksheet actually contribute? It’d be nice to see how it’s all connected, especially when you’re trying to track down problems.

Well, to get a visual, all you need is a little help from a special toolbar.

Go to the View menu, Toolbars submenu, Formula Auditing choice to see the toolbar we need.

The buttons we’re interested in today are on the left side of the toolbar.

To use them, first select a cell containing a formula that references other cells.

Now, to have Excel draw the path of cells that contribute to the formula, click the Trace Precedents button (second from the left).

Instantly you’ll have a visual using cell outlines and arrows showing what other cells in the worksheet contribute to the cell you originally selected.

Click it a second time to have Excel trace another level up in the formulas.

Use the Remove Precedent Arrows button (immediately to the right of Trace Precedents) to reverse the visuals. Each click of the button will remove one level of tracing.

To have Excel give a display of cells containing formulas that depend upon the value in the cell you’ve selected, click the Trace Dependents button (fourth from the left).

It looks a lot like the precedent arrows, but it tells you where the value feeds to instead of where it came from.

To remove these traces, click the next button to the right, which is the Remove Dependent Arrows button.

Hang on, I know it’s been quite a bit, but just one more button will help.

The next button over (the one that looks like an eraser) is the Remove All Arrows button and it will do just that. It removes both precedent and dependent arrows with one click.

And there you have it, a quick and easy way to get a good picture (literally) of where it’s all coming from and leading to.

~ April

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.