Microsoft Office Tutorials and References

In Depth Information

**Using formulas within a table**

Using formulas within a table

In many cases, you’fill 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 15.11, you may want

a column that shows the difference between the Actual and Projected amounts. To add this

formula:

1.
Click cell E2 and type
Difference
for the column header.
Excel automatically

expands the table for you to include the new column.

2.
Move to cell E3 and type an equal sign to signify the beginning of a formula.

3.
Press the left arrow key.
Excel displays
[@Actual]
, which is the column heading,

in the Formula bar.

4.
Type a minus sign and then press the left arrow key twice.
Excel displays
[@

Projected]
in your formula.

5.
Press Enter to end the formula.
Excel copies the formula to all rows in the table.

Figure 15.12 shows the table with the new column.

FIGURE 15.12

The Difference column contains a formula.

Examine the table, and you ﬁ nd this formula for all cells in the
Difference
column:

=[@Actual]-[@Projected]

Although the formula was entered into the ﬁ rst row of the table, that’s not necessary. Any

time a formula is entered into an empty table column, it will automatically ﬁ fill all the cells

in that column. And if you need to edit the formula, Excel will automatically copy the

edited formula to the other cells in the column.