XL98: How to Use PivotTable Calculated Fields and Calculated Items

ID: Q181219


The information in this article applies to:
  • Microsoft Excel 98 Macintosh Edition


SUMMARY

Microsoft Excel 98 Macintosh Edition introduces two new features that you can use to customize PivotTables; these features are calculated fields and calculated items. This article describes the function of each feature and includes examples for using the features in PivotTables.


MORE INFORMATION

Calculated Fields

A calculated field is a user-defined field in a PivotTable that can perform calculations by using the contents of other fields in the PivotTable. A calculated field formula can refer to one or more fields. For example, a calculated field named "Profit" that you define as follows
=Sales-Expense
refers to two fields named "Sales" and "Expense" in the PivotTable.

Example:

The following example uses a calculated field in a PivotTable. This example subtracts the values for the Expense field from the values for the Sales field to give a subtotal for a new field named "Profit." To see this example, follow these steps:
  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 then click Calculated Field.


  5. In the "Insert Calculated Item in 'Region'" dialog box, type Profit in the Name box.


  6. In the Formula box, type =Sales-Expenses and click OK.

    Or, you can select the field name in the Fields box. To insert the selected field in the formula, click Insert Field.


The PivotTable displays the Profit calculated field as a new field under Data. The resulting 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 

Calculated Items

A calculated item is a user-defined item in a PivotTable field that can perform calculations by using the contents of other fields and items in the PivotTable. A calculated item formula can 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."

Example:

The following example uses a calculated item in a PivotTable. This example totals the values for two items, North and West, in the Region field to give a total for the new region named "NorthWest." To see this example, follow these steps:
  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 appears 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 then click Calculated Item.


  5. In the "Insert Calculated Item in 'Region'" dialog box, type NorthWest in the Name box.


  6. In the Formula box, type =North+West and click OK.

    Or, you can 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 in the formula, click Insert Item.


The PivotTable displays the calculated item as a new region. The resulting 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 


REFERENCES

For more information about performing calculations in PivotTables, click Contents And Index on the Help menu (or on the Balloon Help menu if you are using a version of the Macintosh operating system earlier than 8.0), click the Index button in Microsoft Excel Help, type the following text

pivottables, calculated fields

-or-

pivottables, calculated items
and then click Show Topics. Select the "Create a calculated field in a PivotTable" or "Create a calculated item in a PivotTable" topic, and click Go To. If you are unable to find the information you need, ask the Office Assistant.

Additional query words: XL98 8.0 calculation pivot table

Keywords : kbdta xlformula xlpivot
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbhowto


Last Reviewed: January 13, 2000
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.