Microsoft KB Archive/161882

= XL97: Using PivotTable Calculated Fields and Calculated Items =

Article ID: 161882

Article Last Modified on 10/22/2000

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q161882



SUMMARY
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.



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."

  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  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.  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  After the PivotTable appears, click any item under Region. On the PivotTable toolbar, click PivotTable, point to Formulas and click Calculated Field. In the "Insert Calculated Item in 'Region'" dialog box, in the Name box, type Profit . 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.</li></ol>

The PivotTable displays the calculated field Profit as a new field under Data. This PivotTable resembles the following table. <pre class="fixed_text">   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 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."

<ol>  In a new workbook type the following data: <pre class="fixed_text">     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 </li> 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.</li>  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: <pre class="fixed_text">     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 </li> After the PivotTable appears, click any item under Region. On the PivotTable toolbar, click PivotTable, point to Formulas, and click Calculated Item.</li> In the "Insert Calculated Item in 'Region'" dialog box, in the Name box, type NorthWest .</li> 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.</li></ol>

The PivotTable displays the calculated item as a new region. The table resembles the following table. <pre class="fixed_text">      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

<div class="references_section">