Page 365 - Excel 2013 All-in-One For Dummies

After applying the new conditional formatting rule to a cell range, you can test it out by deliberately entering an error value into one of the cells refer- enced in one of the formulas in that range now covered by the “white-out” conditional formatting. Entering the #NA error value in one of these cells with the =NA() function is perhaps the easiest to do. Instead of seeing #NA values spread throughout the cell range, the cells should now appear empty because of the white font applied to all the #N/As, rendering them, for all intents and purposes, invisible.
Formula Auditing
If you don’t happen to trap those pesky error values before they get out into the spreadsheet, you end up having to track down the original cell that caused all the commotion and set it right. Fortunately, Excel offers some very effective formula-auditing tools for tracking down the cell that’s causing your error woes by tracing the relationships between the formulas in the cells of your worksheet. By tracing the relationships, you can test formulas to see which cells, called direct precedents in spreadsheet jargon, directly feed the formulas and which cells, called dependents (nondeductible, of course), depend on
the results of the formulas. Excel even offers a way to visually backtrack the potential sources of an error value in the formula of a particular cell.
The formula-auditing tools are found in the command buttons located in the Formula Auditing group on the Formulas tab of the Ribbon. These command buttons include the following:
✦ TracePrecedents:Whenyouclickthisbutton,Exceldrawsarrowstothe cells (the so-called direct precedents) that are referred to in the formula inside the selected cell. When you click this button again, Excel adds “tracer” arrows that show the cells (the so-called indirect precedents) that are referred to in the formulas in the direct precedents.
✦ Trace Dependents: When you click this button, Excel draws arrows from the selected cell to the cells (the so-called direct dependents) that use, or depend on, the results of the formula in the selected cell. When you click this button again, Excel adds tracer arrows identifying the cells (the so-called indirect dependents) that refer to formulas found in the direct dependents.
✦ Remove Arrows: Clicking this button removes all the arrows drawn,
no matter what button or pull-down command you used to put them there. Click the drop-down button attached to this button to display
a drop-down menu with three options: Remove Arrows to remove all arrows (just like clicking the Remove Arrows command button); Remove Precedent Arrows to get rid of the arrows that were drawn when you
Formula Auditing 347
Book III Chapter 2
Logical Functions and Error Trapping