Microsoft Office Tutorials and References

In Depth Information

**Adding Computational Fields to the Table**

The next step is to add the computational ﬁ elds % of Quota in column I and Grade in

column J. The ﬁ rst computational ﬁ eld involves dividing the YTD Sales in column H by the

Quota in column G. The second computational ﬁ eld involves a table lookup to determine a

grade based upon the % of Quota in column I.

Adding new ﬁ elds to a table in a worksheet illustrates another of Excel’s powerful table capabilities. As shown in

the following steps, if you add a new column heading in a column adjacent to the current column headings in the table,

then Excel automatically adds the adjacent column to the table’s range and copies the format of the table heading to the

new column headings. Adding a new row to a table works in a similar manner.

The ﬁ rst step in adding the two new ﬁ elds is to enter the two column headings, or ﬁ eld names, in cells I8 and J8,

enter the ﬁ rst % of Quota formula in cell I9, and then format the two cells immediately below the new column head-

ings. The formula for the % of Quota in cell I9 is YTD Sales / Quota or =H9 / G9. After the formula is entered in

cell I9, the formula is automatically copied to the range I10:I21. When you enter a formula in the ﬁ rst row of a ﬁ eld,

Excel creates a calculated column. A calculated column is a column in a table in which each row uses a common

formula that references other ﬁ elds in the table.

1

•
Select cell I8, type
%

of Quota
, click cell

J8, type
Grade
.

•
Select cell I9, enter

=h9 / g9
as the for-

mula, and then click

the Enter button

on the formula bar

(Figure 5–19).

new columns

automatically

added to table

formula is

=H9/G9

Excel automatically

copies formula to

all rows in table in

column I

Figure 5–19