стр. 22 |

TRACING THE CELL REFERENCES USED IN A

5.1

FORMULA

Click on the cell that contains the formula whose references need to be

visually traced. Pick the menu option TOOLS/AUDITING/TRACE

PRECEDENTS. (For a pictorial reproduction of this, see Figure 70.)

75

Statistical Analysis with Excel

Figure 70: Tracing precedents. These options are from Excel versions prior to Excel XP.

Figure 71: Excel XP offers several error-checking and debugging tools.

As shown in Figure 72, blue arrows will trace the references.

If a group of cells is referenced, then the group will be marked by a blue

rectangle. The two rectangular areas are referenced in the formula.

In Volume 3: Excel- Beyond The Basics, you are taught the simple process

through which you can select all the cells whose formulas are precedents

of the active cell.

76

Chapter 5: Tracing Cell References & Debugging Formula Errors

Figure 72: The arrows define and trace all the cells/ranges referenced in the active cell

TRACING THE FORMULAS IN WHICH A

5.2

PARTICULAR CELL IS REFERENCED

You may want to do the oppositeвЂ” see which formulas reference a

particular cell.

вЂў First, click on the cell of interest.

Then, pick the menu option TOOLS/AUDITING/TRACE

вЂў

DEPENDENTS as shown in Figure 73. Now the arrows will go

from the active cell to all the cells that have formulas that use

the active cell.

77

Statistical Analysis with Excel

Figure 73: Tracing Dependents. These options are from Excel versions prior to Excel XP.

Remove all the auditing arrows by following the menu path

TOOLS/AUDITING/REMOVE ALL ARROWS.

In Volume 3: Excel- Beyond The Basics you learn the simple process

through which you can select all the cells whose formulas are dependents

of the active cell.

THE AUDITING TOOLBAR

5.3

The вЂњAuditingвЂќ toolbar opens automatically when you are using the

auditing option (TOOLS/AUDITING) to review formula references.

Refer to Volume 3: Excel- Beyond The Basics for details on using toolbars.

In the XP version of Excel, you can launch the toolbar through the menu

option TOOLS/AUDITING/SHOW FORMULA AUDITING TOOLBAR.

Figure 74: The вЂњAuditingвЂќ toolbar

78

Chapter 5: Tracing Cell References & Debugging Formula Errors

WATCH WINDOW (ONLY AVAILABLE IN THE XP

5.4

VERSION OF EXCEL)

The window is accessed through the menu path TOOLS/ AUDITING/

SHOW WATCH WINDOW, or VIEW/ TOOLBARS/ WATCH WINDOW.

Figure 75: The Watch Window may not display correctly. Use the mouse to drag the walls of

the dialog to a workable size.

Add one cell on whose values you want to keep tabs.

The value will be shown in the Watch Window so that you can see the

value even if you are working on cells or sheets that are far from the cell

whose value is being вЂњwatched.вЂќ

Figure 76: Add Watch

стр. 22 |