May 25, 2008

Tracing the Path

Do you sometimes use complicated MS Excel formulas? You know, where a formula references cells that, in turn, reference other cells, which can lead 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 with the reported values.

Ever find yourself wondering just where that value really came 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 solve a problem.

Well, to get a visual, all you need to know are the right buttons to click.

In older versions of Excel, you're looking for the View menu, Toolbars submenu, Formula Auditing choice.

If you're using Excel 2007, you need the Formulas ribbon, Formula Auditing section.

To use the auditing tools, you must 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. In older versions, it's the second button from the left and in Excel 2007, it's the top button on the left side.

Instantly, you'll have a visual with cell outlines and arrows that shows you what other cells in the worksheet contribute to the cell you originally selected.

Click the button a second time to have Excel trace another level of formulas.

In older versions of Excel, use the Remove Precedent Arrows button to reverse the visuals. Each click of that button will remove one level of tracing.

In Excel 2007, you'll need to click the down arrow on the Remove Arrows button and then choose Remove Precedent Arrows.

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. (In where to get viagra older versions of Excel, you'll only see the picture, but it looks the same).

The result looks a lot like the precedent arrows, but it tells you where the value feeds go, instead of where they came from.

To remove those traces, click the Remove Dependent Arrows button. (In Excel 2007, you'll find it in the Remove Arrows options list).

I know this has been quite a bit to take in, but I have one more button to show you!

In older versions of Excel, 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 the precedent and dependent arrows with just one click.

In Excel 2007, you can achieve a complete erase of all the arrows by clicking on the Remove Arrows button instead of its down arrow.

That's it. A quick and easy way to get a good picture of where everything is coming from and where it's going!