Microsoft Office Tutorials and References
In Depth Information
Chapter 3: Formulas and Functions for Crunching Numbers
As the figures in the Actual Income column (column C) are updated, figures
in the Over/Under Budget column (column E) and the Total Income row
(row 8) change instantaneously. These figures change instantaneously because
the formulas refer to the numbers in cells, not to unchanging numbers
(known as constants ).
Figure 3-3 shows the formulas used to calculate the data in the worksheet in
Figure 3-2. In column E, formulas deduct the numbers in column D from the
numbers in column C to show where the PTA over- or under-budgeted for
the different sources of income. In row 8, you can see how the SUM function
is used to total cells in rows 3 through 7. The end of this chapter explains
how to use functions in formulas.
in Figure 3-2.
Excel is remarkably good about updating cell references in formulas when
you move cells. To see how good Excel is, consider what happens to cell
addresses in formulas when you delete a row in a worksheet. If a formula
refers to cell C1 but you delete row B, row C becomes row B and the value
in cell C1 changes addresses from C1 to B1. You would think that references
in formulas to cell C1 would be out of date, but you would be wrong. Excel
automatically adjusts all formulas that refer to cell C1. Those formulas now
refer to cell B1 instead.
In case you’re curious, you can display formulas in worksheet cells instead
of the results of formulas, as was done in Figure 3-3, by pressing Ctrl+’
(apostrophe) or clicking the Show Formulas button on the Formulas tab (you may
have to click the Formula Auditing button first, depending on the size of
your screen). Click the Show Formulas button a second time to see formula