# XL97: Using PivotTable Calculated Fields and Calculated Items

This article was previously published under Q161882
This article has been archived. It is offered "as is" and will no longer be updated.
##### SUMMARY
Microsoft Excel 97 introduces two new features for customizing PivotTables;these features are calculated fields and calculated items. This articledescribes the function of each feature and provides examples for using thefeatures in PivotTables.

### Calculated Fields

A Calculated Field is a user-defined field in a PivotTable that can performcalculations using the contents of other fields in the PivotTable.Calculated field formulas can refer to one or more fields. For example, acalculated field named "Profit" that you define as:
=Sales-Expense
refers to two fields in the PivotTable named "Sales" and "Expense."

The following example uses a calculated field in a PivotTable. This examplesubtracts the two fields Sales and Expense to give a subtotal for the newfield named "Profit."

1. In a new workbook type the following data:
`   A1 : Month   B1 : Region   C1 :Sales   D1 : Expenses   A2 : Jan     B2 : East     C2 : 1100   D2 :      400   A3 : Jan     B3 : West     C3 : 2400   D3 :      700   A4 : Jan     B4 : North    C4 : 3700   D4 :      900   A5 : Jan     B5 : South    C5 : 2800   D5 :      800   A6 : Feb     B6 : East     C6 : 2300   D6 :      700   A7 : Feb     B7 : West     C7 : 2400   D7 :      800   A8 : Feb     B8 : North    C8 : 3100   D8 :     1100   A9 : Feb     B9 : South    C9 : 2000   D9 :      600   A10: Mar     B10: East     C10: 1500   D10:      300   A11: Mar     B11: West     C11: 2600   D11:      500   A12: Mar     B12: North    C12: 2200   D12:      400   A13: Mar     B13: South    C13: 3900   D13:     1300						`
2. Select cell A1. On the Data menu, click PivotTable Report. In step 1 of the wizard, click Microsoft Excel list or database and click Next.
3. In step 2 of the wizard, click Next for the Range \$A\$1:\$D\$13. In step 3 of the wizard, drag the field Month to the COLUMN area. Drag the field Region to the ROW area and drag the fields Sales and Expenses to the DATA area. Click Finish. The PivotTable appears on a new worksheet and resembles the following table.
`                                         Month      Region   Data                Jan    Feb    Mar    Grand Total      East     Sum of Sales       3700   3100   2200           9000               Sum of Expenses     900   1100    400           2400      North    Sum of Sales       1100   2300   1500           4900               Sum of Expenses     400    700    300           1400      South    Sum of Sales       2400   2400   2600           7400               Sum of Expenses     700    800    500           2000      West     Sum of Sales       2800   2000   3900           8700               Sum of Expenses     800    600   1300           2700      Total Sum of Sales         10000   9800  10200          30000      Total Sum of Expenses       2800   3200   2500           8500`
4. After the PivotTable appears, click any item under Region. On the PivotTable toolbar, click PivotTable, point to Formulas and click Calculated Field.
5. In the "Insert Calculated Item in 'Region'" dialog box, in the Name box, type Profit.
6. In the Formula box, type =Sales-Expenses and click Ok.

You may alternately select the field name in the Fields box. To insert the selected field in the formula, click Insert Field.
The PivotTable displays the calculated field Profit as a new field underData. This PivotTable resembles the following table.
`    Month   Region   Data                Jan    Feb    Mar    Grand Total   East     Sum of Sales       3700   3100   2200           9000            Sum of Expenses     900   1100    400           2400            Sum of Profit      2800   2000   1800           6600   North    Sum of Sales       1100   2300   1500           4900            Sum of Expenses     400    700    300           1400            Sum of Profit       700   1600   1200           3500   South    Sum of Sales       2400   2400   2600           7400            Sum of Expenses     700    800    500           2000            Sum of Profit      1700   1600   2100           5400   West     Sum of Sales       2800   2000   3900           8700            Sum of Expenses     800    600   1300           2700            Sum of Profit      2000   1400   2600           6000   Total Sum of Sales         10000   9800  10200          30000   Total Sum of Expenses       2800   3200   2500           8500   Total Sum of Profit         7200   6600   7700          21500 6600   7700          21500				`

### Calculated Items

A calculated item is a user-defined item in a PivotTable field thatcan perform calculations using the contents of other fields and items inthe PivotTable. Calculated item formulas can each include only items fromthe field in which you create the calculated item. For example, you candefine a calculated item named "NorthWest" as "=North+West" in the fieldnamed "Region."

The following example uses a calculated item in a PivotTable. This exampletotals two items, North and West, in the Region field to give a total forthe new region named "NorthWest."

1. In a new workbook type the following data:
`      A1 : Month   B1 : Region   C1 :Sales      A2 : Jan     B2 : East     C2 : 1100      A3 : Jan     B3 : West     C3 : 2400      A4 : Jan     B4 : North    C4 : 3700      A5 : Jan     B5 : South    C5 : 2800      A6 : Feb     B6 : East     C6 : 2300      A7 : Feb     B7 : West     C7 : 2400      A8 : Feb     B8 : North    C8 : 3100      A9 : Feb     B9 : South    C9 : 2000      A10: Mar     B10: East     C10: 1500      A11: Mar     B11: West     C11: 2600      A12: Mar     B12: North    C12: 2200      A13: Mar     B13: South    C13: 3900						`
2. Select cell A1. On the Data menu, click PivotTable Report. In step 1 of the wizard, click Microsoft Excel list or database and click Next.
3. In step 2 of the wizard, click Next for the Range \$A\$1:\$C\$13. In step 3 of the wizard, drag the field Month to the COLUMN area. Drag the field Region to the ROW area and drag the field Sales to the DATA area. Click Finish. The PivotTable should appear on a new worksheet and resembles the following table:
`      Sum of Sales      Month      Region            Jan    Feb    Mar    Grand Total      East             3700   3100   2200           9000      North            1100   2300   1500           4900      South            2400   2400   2600           7400      West             2800   2000   3900           8700      Grand Total     10000   9800  10200          30000						`
4. After the PivotTable appears, click any item under Region. On the PivotTable toolbar, click PivotTable, point to Formulas, and click Calculated Item.
5. In the "Insert Calculated Item in 'Region'" dialog box, in the Name box, type NorthWest.
6. In the Formula box, type =North+West and click OK.

You may alternately select the items for each field by clicking the field name in the Fields box, and then clicking the corresponding item for that field in the Items box. To insert the selected item into the formula, click Insert Item.
The PivotTable displays the calculated item as a new region. The tableresembles the following table.
`       Sum of Sales      Month      Region            Jan    Feb    Mar    Grand Total      East             3700   3100   2200           9000      North            1100   2300   1500           4900      South            2400   2400   2600           7400      West             2800   2000   3900           8700      NorthWest        3900   4300   5400          13600      Grand Total     13900  14100  15600          43600				`
##### REFERENCES
For more information about performing calculations in PivotTables, clickthe Index tab in Microsoft Excel 97 Help, type the following text
pivottables, calculated fields
-or-
pivottables, calculated items
and then double-click the selected text to go to the "Create a calculatedfield in a PivotTable" or "Create a calculated item in a PivotTable" topic.
97 XL97 8.0 calculation pivot table
Properties

Article ID: 161882 - Last Review: 12/04/2015 16:05:52 - Revision: 1.0

Microsoft Excel 97 Standard Edition

• kbnosurvey kbarchive kbhowto kbualink97 KB161882