Tip #65 - Excel Formula Auditing

100 Computer Tips in 100 Days

Tip #65 - Excel Formula Auditing
Tracing Dependents and Precedents

There are a couple of auditing tools available in Excel that are of great help when you are troubleshooting excel formulas, when you need to explain a spreadsheet to a co-worker or when you need to audit a spreadsheet for errors.

The two primary tools are tracing dependents and tracing precedents. Both of these tools are designed to identify graphically, using a series of arrows, the relationship of the active cell with other cells in your workbook. When you trace dependents, all of the cells that are dependent on (will affect the results) the contents of the active cell are identified. Conversely, when you trace precedents, all of the cells that feed into or precede the active cell are identified with arrows. When you trace precedents the active cell must contain a formula.

Formula Auditing from the active cell in D column.

To try this out, open one of your worksheets that has formulas. Click on a cell that is used in a formula. Click the Formula tab, Formula Auditing group, Trace Dependents (View menu, toolbars, Formula Auditing in the Apple version). You should see arrows pointing to the first level of dependency. Clicking on the Trace Dependents option again will show the second level of dependency. Continue clicking to show additional dependent cells. If you were to print your worksheet with the dependency arrows showing, they will be visible. If you wanted to print your worksheet at this point it would be helpful to print the row/column headings (the row numbers and the column letters) to help you identify the location of the cells. To do this click on the Page Layout tab, Sheet Options group, Print Headings (File menu, Page Setup, Sheet tab).

Use the Remove Arrows option to turn off the display of the relationship arrows. If you are a veteran Excel user I guarantee you’ll use this tip a lot. The Trace Precedents works the same way, click in a cell with a formula and the cells that are used in that formula will be identified. Continue clicking to see other cells that also feed into that result.

I've included a picture of some Formula Auditing Art! Yes, this is done by people who have a little too much time on their hands but it's kind of cool!

Formula Auditing Art

Have you bought my book yet? If not, why not??  100 Amazing Computer Tips - Shortcuts, Tricks, and Advice to Help Everyone from Novice to Professional, is available NOW at Amazon.com and Barnes & Noble in both print ($14.99) and ebook ($7.99) formats. There's also a handy link on top right side of this window for your shopping convenience. If you've bought my book, THANK YOU! Please consider writing a review on Amazon.com.

Happy computing!

Diane

Don't forget to click the Subscribe to 100 Computer Tips in 100 Days via email link on the right side of the screen so you get email alerts when I add a new post.