Auditing and Documenting Worksheets

Office Excel 2007 has a number of powerful and flexible features that help you audit and debug your worksheets and document your work. Most of the Excel auditing features appear on the Formulas tab in the Formula Auditing group, which is shown in Figure 8-52.

Figure 8-52: The Formula Auditing group on the Formulas tab provides access to most of the Excel 2007 auditing features.

Checking for Errors

Click the Error Checking button to quickly find any error values displayed on the current worksheet and display the Error Checking dialog box, as shown in Figure 8-53. The first erroneous cell in the worksheet is selected, and its contents are displayed in the dialog box, along with a suggestion about the nature of the problem.

Figure 8-53: The Error Checking dialog box helps you figure out what's wrong with formulas that display error values.

When your problem appears in the dialog box, the following selections are available:

• Help On This Error displays a Help topic relating to the problem cell.

• Show Calculation Steps displays the Evaluate Formula dialog box. See "Evaluating and Auditing Formulas" on the next page.

• Ignore Error skips the selected cell. To "unignore" errors, click Options, and then click Reset Ignored Errors.

• Edit In Formula Bar opens the selected cell in the formula bar for editing. When you're finished, click Resume (the Help On This Error button changes to Resume).

Click the Previous and Next buttons to locate additional errors on the current worksheet. Click the Options button to display the Formulas category in the Excel Options dialog box, shown in Figure 8-54. Select or clear the check boxes in the two Error Checking areas to determine the type of errors to look for and the way they are processed. Click the Reset Ignored Errors button if you want to recheck or if you clicked the Ignore Error button in the Error Checking dialog box by mistake.

Figure 8-54: Click Options in the Error Checking dialog box to specify error-checking rules.

Evaluating and Auditing Formulas

Sometimes it's difficult to tell what's going on in a complex nested formula. A formula is nested when parts of it (called arguments) can be calculated separately. For example, in the formula =IF(Pay_Num<>"",Scheduled_Monthly_Payment,""), the named reference Pay_Num indicates a cell that must contain a value in order for the rest of the formula to function. To make this formula easier to read, you can substitute this expression with its result-in this case, 1 (indicating that the expression is TRUE). The formula would then be =IF(1<>"",Scheduled_Monthly_Payment,"").

When you click the Evaluate Formula button on the Formulas tab, you can resolve each nested expression one at a time in complex formulas. Figure 8-55 shows the Evaluate Formula dialog box in action.

Figure 8-55: Click the Evaluate Formula button on the Formulas tab to systematically inspect nested formulas.

For more information about formulas and arguments, see Chapter 12, "Building Formulas."

Click Evaluate to replace each calculable argument with its resulting value. You can click Evaluate as many times as necessary, depending on how many nested levels exist in the selected formula. For example, if you click Evaluate in Figure 8-55, Excel replaces the aforementioned Pay_Num reference with its value. Clicking Evaluate again calculates the next level, and so on, until you reach the end result, which in this case is $188.71, as shown in Figure 8-56.

Figure 8-56: Each time you click the Evaluate button, Excel calculates another nested level in the selected formula.

On the CD You'll find the Loan Amortization. xlsm file in the Sample Files section of the companion CD.

Eventually, clicking Evaluate results in the formula's displayed value, and the Evaluate button changes to Restart, letting you repeat the steps. Click Step In to separate each calculable reference into separate boxes, making the hierarchy more apparent. In our example, the first evaluated reference is to a cell range, which cannot be further evaluated. If the reference is to a cell containing another formula, its address appears in the Evaluate Formula dialog box, as shown in Figure 8-57. Where there are no more steps to be displayed, click Step Out to close the Step In box and replace the reference with the resulting value.

Figure 8-57: Use Step In and Step Out to display calculable arguments separately.

Watching Formulas

Sometimes you might want to keep an eye on a formula as you make changes to other parts of a worksheet, or even when working on other workbooks that supply information to a worksheet. Instead of constantly having to return to the formula's location to see the results of your ministrations, you can use the Watch Window, which provides remote viewing for any cell on any open worksheet.

Select a cell you want to keep an eye on, and click Watch Window on the Formulas tab. Then click Add Watch in the Watch Window, as shown in Figure 8-58.

Figure 8-58: Select a cell, and click Watch Window to keep an eye on it, no matter where you are currently working.

You can click a cell you want to watch either before or after you display the Add Watch dialog box. Click Add to insert the cell information in the Watch Window. You can dock the Watch Window, as shown in Figure 8-59. You can change its size by dragging its borders or dragging it away from its docked position.

Figure 8-59: The Watch Window displays all the current information for the watched formula.

While your workbook is still open, you can select any item in the Watch Window list and delete it by clicking Delete Watch. The Watch Window button is a toggle-click it again to close the window; or click the Close button at the top of the Watch Window. When you close a workbook, Excel removes any watched cells the workbook contains from the Watch Window list.

Tracing Cell References

If you've ever looked at a large worksheet and wondered how you could get an idea of the data flow-that is, how the formulas and values relate to one another-you'll appreciate cell tracers. You can also use cell tracers to help find the source of those pesky errors that occasionally appear in your worksheets. The Formula Auditing group on the Formulas tab contains three buttons that control the cell tracers: Trace Precedents, Trace Dependents, and Remove Arrows.

Inside Out-Understanding Precedents and Dependents

The terms precedent and dependent crop up quite often in this section. They refer to the relationships that cells containing formulas create with other cells. A lot of what a spreadsheet is all about is wrapped up in these concepts, so here's a brief description of each term:

• Precedents are cells whose values are used by the formula in the selected cell. A cell that has precedents always contains a formula.

• Dependents are cells that use the value in the selected cell. A cell that has dependents can contain either a formula or a constant value.

For example, if the formula =SUM(A1:A5) is in cell A6, cell A6 has precedents (A1:A5) but no apparent dependents. Cell A1 has a dependent (A6) but no apparent precedents. A cell can be both a precedent and a dependent if the cell contains a formula and is also referenced by another formula.

Tracing Dependent Cells

In the worksheet in Figure 8-60, we selected cell B2, which contains the hourly rate value. To find out which cells contain formulas that use this value, click the Trace Dependents button. Although this worksheet is elementary to make it easier to illustrate the cell tracers, consider the ramifications of using the cell tracers in a large and complex worksheet.

Figure 8-60: When you trace dependents, arrows point to formulas that directly refer to the selected cell.

On the CD You'll find the Audit. xlsx file in the Sample Files section of the companion CD.

The tracer arrows indicate that cell B2 is directly referred to by the formulas in cells C5, C6, C7, and C8. If you click Trace Dependents again, another set of arrows appears, indicating the next level of dependencies-or indirect dependents. Figure 8-61 shows the results.

Figure 8-61: When you click Trace Dependents again, arrows point to the next level of formulas, ones that indirectly refer to the selected cell.

One handy feature of the tracer arrows is that you can use them to navigate, which can be advantageous in a large worksheet. For example, in Figure 8-61 with cell B2 still selected, double-click the arrow pointing from cell B2 to cell C8. The selection jumps to the other end of the arrow, and cell C8 becomes the active cell. Now if you double-click the arrow pointing from cell C8 to cell E8, the selection jumps to cell E8. If you double-click the same arrow again, the selection jumps back to cell C8. If you double-click an arrow that extends beyond the screen, the window shifts to display the cell at the other end. You can use this feature to jump from cell to cell along a path of precedents and dependents.

Clearing Tracer Arrows

As you trace precedents or dependents, your screen quickly becomes cluttered, making it difficult to discern the data flow for particular cells. To remove all the tracer arrows from the screen, click the Remove Arrows button in the Formula Auditing group. Alternatively, you can click the small downward-pointing arrow next to the Remove Arrows button to display the Remove Arrows menu, where you can be more selective, using Remove Precedent Arrows or Remove Dependent Arrows.

Tracing Precedent Cells

You can also trace in the opposite direction by starting from a cell that contains a formula and tracing the cells that are referred to in the formula. In Figure 8-62, we selected cell E5, which contains one of the net wages formulas, and then clicked Trace Precedents twice to show the complete precedent path.

Figure 8-62: When you trace precedents, arrows point from all the cells to which the formula in the selected cell directly refers.

This time, arrows appears with dots in cells B2, B5, C5, and D5, indicating that all these cells are precedents to the selected cell. Notice that the arrows still point in the same direction-toward the formula and in the direction of the data flow-even though we started from the opposite end of the path.

Tracing Errors

Suppose your worksheet displays error values like the ones shown in Figure 8-63. To trace one of these errors to its source, select a cell that contains an error, and click Trace Error, located on the Error Checking menu in the Formula Auditing group on the Formulas tab, as shown in Figure 8-53 on page 242.

Figure 8-63: Cells with error values display smart tag action menus.

Notice that the cells containing errors display smart tag indicators in their upper-left corners, as shown in Figure 8-63, and when you select one of them, the smart tag action menu appears. The smart tag action menu displays applicable options, including Trace Error, as shown in Figure 8-64.

Figure 8-64: Select a cell that contains an error value, and click Trace Error to display arrows that trace the error to its source.

Excel selects the cell that contains the first formula in the error chain and draws red arrows from that cell to the cell you selected. Excel draws blue arrows to the cell that contains the first erroneous formula from the values the formula uses. It's up to you to determine the reason for the error; Excel takes you to the source formula and shows you the precedents. In our example, the error is caused by a space character inadvertently entered in cell B6, replacing the hours-worked figure. This is a common, vexing problem, because cells containing space characters appear to be empty, but a truly empty cell would not have produced an error in this case.

Tracing References to Other Worksheets

If a cell contains a reference to a different worksheet or to a worksheet in another workbook, a dashed tracer arrow appears with a small icon attached, as shown in Figure 8-65. You cannot continue to trace precedents using the same procedure from the active cell when a dashed tracer arrow appears.

Figure 8-65: If you trace the precedents of a cell that contains a reference to another worksheet or workbook, a special tracer arrow appears.

If you double-click a dashed tracer arrow, the Go To dialog box appears, with the reference displayed in the Go To list. You can select the reference in the list and click OK to activate the worksheet or workbook. However, if the reference is to another workbook that is not currently open, an error message appears.

Adding Comments to Cells

Someday, someone else might need to use your workbooks, so it's good to be clear and to explain everything thoroughly. You can attach comments to cells to document your work, explain calculations and assumptions, or provide reminders. Select the cell you want to annotate, and click the New Comment button in the Comments group on the Review tab (the button changes to Edit Comment after you click it). Then type your message in the box that appears, as shown in Figure 8-66.

Figure 8-66: You can attach comments to cells to help document your worksheet.

When you add a comment to a cell, your name appears in bold type at the top of the comment box. You can specify what appears here by clicking the Microsoft Office Button, Excel Options and in the Personalize category typing your name (or any other text) in the User Name box. Whatever you type here appears at the top of the comment box, followed by a colon. Although you can attach only one comment to a cell, you can make your comment as long as you like. If you want to begin a new paragraph in the comment box, press Enter. When you're finished, you can drag the handles to resize the comment box.

Note 

Ordinarily, a small red triangle appears in the upper-right corner of a cell, indicating the presence of a comment. When you rest the pointer on a cell displaying this comment indicator, the comment appears. To control the display of comments, click the Microsoft Office Button, Excel Options and then the Advanced category. In the Display area, select one of the options under For Cells With Comments, Show.

Tweaking Your Comments

After you've added text to your comments, nothing is set in stone. You can work with comments using the buttons in the Comments group on the Review tab:

• New Comment/Edit Comment Click this button to add a comment to the selected cell. If the selected cell already contains a comment, this button changes to Edit Comment, which opens the comment for editing.

• Previous and Next Click these buttons to open each comment in the workbook for editing, one at a time. Even if your comments appear on several worksheets in the same workbook, these buttons let you jump directly to each one in succession without using the sheet tabs.

• Show/Hide Comment Click this button to display (rather than open for editing) the comment in the selected cell. This button changes to Hide Comment if the comment is currently displayed.

• Show All Comments Click this button to display all the comments on the worksheet at once.

• Delete Click this button to remove comments from all selected cells.

• Show Ink Click this button to show or hide any ink annotations (Tablet PC only).

Printing Comments

To print comments, follow these steps:

1. Click the Page Layout tab on the Ribbon, and click the Dialog Box Launcher in the Page Setup group (the little icon to the right of the group name).

2. Click the sheet tab, and select one of the options in the Comments drop-down list.

   The At End Of Sheet option prints all the comments in text form after the worksheet is printed. The As Displayed On Sheet option prints comments as they appear on the screen (as text boxes) if you display all the comments at once on the worksheet. Be careful, however, because comments printed this way can obscure some contents of the worksheet; and if your comments are clustered together, they might overlap.

3. Click the Print button in the Page Setup dialog box to display the Print dialog box, where you can select additional options before sending your worksheet to the printer.

For more information about printing, see Chapter 11, "Printing and Presenting."