Chapter 9: Introducing Formulas and Functions
9
Using formulas within a table
In many cases, you’ll want to use formulas within a table to perform calculations that use
other columns in the table. For example, in the table shown in Figure 9.10, you may want a
column that shows the difference between the Actual and Projected amounts. To add this
formula, follow these steps:- Activate cell E2 and type Difference for the column header. After you press
Enter, Excel automatically expands the table for you to include the new column. - Move to cell E3 and type an equal sign to signify the beginning of a formula.
- Press the left arrow key. Excel displays [@Actual], which is the column heading,
in the Formula bar. - Type a minus sign and then press the left arrow key twice. Excel displays [@
Projected] in your formula. - Press Enter to end the formula. Excel copies the formula to all rows in the table.
Figure 9.11 shows the table with the new column.FIGURE 9.11
The Difference column contains a formula.Examine the table, and you will find this formula for all cells in the Difference column:=[@Actual]-[@Projected]Although the formula was entered into the first row of the table, that’s not necessary. Any
time a formula is entered into an empty table column, it will automatically fill all of the
cells in that column. If you need to edit the formula, Excel will automatically copy the
edited formula to the other cells in the column.