Auditing Spreadsheet Formulas

Okay, now here's one I know you'll appreciate. Suppose that you've got a complex spreadsheet that contains lots of formulas. When you look at the numbers, you see results because that's what Quattro Pro displays. And, while you could format the cells to view the formulas, in fact, you really want to know, at a glance, what cells on the spreadsheet are included in a particular formula. You also want to know what formula uses a particular cell. By auditing your spreadsheet, you can find out.

To make this easy for you to see, I'll use a simple spreadsheet like the one shown in Figure 14.4 instead of a complex spreadsheet. In the figure, the grand totals in column F are the sums of the values in rows 5, 6, and 7 respectively. Similarly, the grand totals in row 7 are the sums of the values in Columns B, C, D, E and F respectively.

Figure 14.4. A typical spreadsheet in which you can identify the cells used in each formula.

To identify the cells used by a formula, you trace the formula's precedents in Quattro Pro. Select a cell containing a formula; in Figure 14.5, I selected B7. Then, open the Tools menu, point to Auditing, and click Trace Precedents.

Figure 14.5. Tracing precedents for a formula.

Quattro Pro draws a blue arrow; the head of the arrow points to the selected cell and the other end of the arrow starts at the first cell included in the formula; the line of the arrow runs through the other cells included in the formula. The arrow tells you that the formula in cell B7 uses the values stored in B5 and B6.

Now suppose that you want to know the formulas that use a particular value. To test for dependents, as you do in this test, you can select any cell on the spreadsheet; you don't need to select a cell that contains a formula. In Figure 14.6, I've selected C6. Open the Tools menu, point to Auditing, and click Trace Dependents.

Figure 14.6. Tracing cells that depend on the selected cell.

Quattro Pro draws blue arrows; the heads of the arrows point at cells that contain formulas that include the selected cell. You may also see a black arrow with a small gray box containing a chain link attached at the head of the arrow. This black arrow and the icon displaying the chain link indicate that a formula on another spreadsheet in the notebook or in another notebook uses the selected cell.

Tip

If you click the icon containing the link, Quattro Pro displays the addresses of the cells involved in the link. You can click any cell address to switch to that cell.

To remove auditing arrows, open the Tools menu, point to Auditing, and click Remove All Arrows.