ID: Q161882
The information in this article applies to:
Microsoft Excel 97 introduces two new features for customizing PivotTables; these features are calculated fields and calculated items. This article describes the function of each feature and provides examples for using the features in PivotTables.
A Calculated Field is a user-defined field in a PivotTable that can perform calculations using the contents of other fields in the PivotTable. Calculated field formulas can refer to one or more fields. For example, a calculated field named "Profit" that you define as:
=Sales-Expense
The following example uses a calculated field in a PivotTable. This example subtracts the two fields Sales and Expense to give a subtotal for the new field 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
of the wizard, click Microsoft Excel list or database and click Next.
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
PivotTable toolbar, click PivotTable, point to Formulas and click
Calculated Field.
type "Profit" (without the quotation marks).
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.
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
A calculated item is a user-defined item in a PivotTable field that can perform calculations using the contents of other fields and items in the PivotTable. Calculated item formulas can each include only items from the field in which you create the calculated item. For example, you can define a calculated item named "NorthWest" as "=North+West" in the field named "Region."
The following example uses a calculated item in a PivotTable. This example totals two items, North and West, in the Region field to give a total for the 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
the wizard, click Microsoft Excel list or database and click Next.
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
PivotTable toolbar, click PivotTable, point to Formulas, and click
Calculated Item.
box, type "NorthWest" (without quotation marks).
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.
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
For more information about performing calculations in PivotTables, click the Index tab in Microsoft Excel 97 Help, type the following text
pivottables, calculated fields
-or-
pivottables, calculated items
Additional query words: 97 XL97 8.0 calculation pivot table
Keywords : kbualink97 xlformula xlpivot
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbhowtoLast Reviewed: November 4, 1998