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.
• 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
added to table
copies formula to
all rows in table in