{"id":417,"date":"2008-05-25T17:05:25","date_gmt":"2008-05-25T22:05:25","guid":{"rendered":"http:\/\/alsplace.aldenbaker.com\/alsplace\/microsoft\/ms-office\/ms-excel\/417\/tracing-the-path\/"},"modified":"2008-05-25T17:05:25","modified_gmt":"2008-05-25T22:05:25","slug":"tracing-the-path","status":"publish","type":"post","link":"https:\/\/alsplace.info\/?p=417","title":{"rendered":"Tracing the Path"},"content":{"rendered":"\n\n

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. <\/font><\/p>\n

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. <\/font><\/p>\n

Well, to get a visual, all you need to know are the right buttons to click.<\/font><\/p>\n

In older versions of Excel, you're looking for the View<\/strong> menu, Toolbars<\/strong> submenu, Formula Auditing<\/strong> choice. <\/font><\/p>\n

\n
\n
<\/font><\/div>\n<\/p><\/div>\n<\/blockquote>\n
\n

If you're using Excel 2007, you need the Formulas<\/strong> ribbon, Formula Auditing <\/strong>section.<\/font><\/p>\n<\/p><\/div>\n

\n
\n
<\/div>\n<\/p><\/div>\n<\/blockquote>\n
To use the auditing tools, you must first select a cell containing a formula<\/strong> that references other cells.<\/font> <\/p>\n

Now, to have Excel draw the path of cells that contribute to the formula, click the Trace Precedents<\/strong> 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. <\/font><\/p>\n

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.<\/font><\/p>\n<\/p><\/div>\n

\n
\n
<\/font><\/div>\n<\/p><\/div>\n<\/blockquote>\n
Click the button a second time<\/strong> to have Excel trace another level of formulas.<\/font> <\/p>\n

In older versions of Excel, use the Remove Precedent Arrows<\/strong> button to reverse the visuals. Each click of that button will remove one level of tracing.<\/font><\/p>\n<\/p><\/div>\n

\n
\n
<\/font><\/div>\n<\/p><\/div>\n<\/blockquote>\n
\n

In Excel 2007, you'll need to click the down arrow<\/strong> on the Remove Arrows button<\/strong> and then choose Remove Precedent Arrows<\/strong>.<\/font><\/p>\n<\/p><\/div>\n

\n
\n
<\/div>\n<\/p><\/div>\n<\/blockquote>\n
\n

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<\/strong> button. (In where to get viagra<\/a> older versions of Excel, you'll only see the picture, but it looks the same).<\/font><\/p>\n<\/p><\/div>\n

\n
\n
<\/font><\/div>\n<\/p><\/div>\n<\/blockquote>\n
The result looks a lot like the precedent arrows, but it tells you where the value feeds go, instead of where they came from.<\/font> <\/p>\n

To remove those traces, click the Remove Dependent Arrows button<\/strong>. (In Excel 2007, you'll find it in the Remove Arrows options list).<\/font><\/p>\n

I know this has been quite a bit to take in, but I have one more button to show you! <\/font><\/p>\n

In older versions of Excel, the next button over (the one that looks like an eraser) is the Remove All Arrows button<\/strong> and it will do just that. It removes both the precedent and dependent arrows with just one click. <\/font><\/p>\n

In Excel 2007, you can achieve a complete erase of all the arrows by clicking on the Remove Arrows button<\/strong> instead of its down arrow.<\/font><\/p>\n

That's it. A quick and easy way to get a good picture of where everything is coming from and where it's going!<\/font><\/p>\n<\/p><\/div>\n\n

\nBookmark to:<\/em><\/strong><\/a>\n
\n
\n
\n
\nHide Sites<\/a>\n<\/div>\n<\/div>\n\n