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.
Back to the top
Calculated Fields
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
refers to two fields in the PivotTable named "Sales" and "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
|
| 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 under
Data. 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
Back to the top
Calculated Items
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
|
| 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 table
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
NorthWest 3900 4300 5400 13600
Grand Total 13900 14100 15600 43600
Back to the top
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
and then double-click the selected text to go to the "Create a calculated
field in a PivotTable" or "Create a calculated item in a PivotTable" topic.
Back to the top
Help and Support
