Change how PivotTable or PivotChart data is summarized

Microsoft Office Excel 2003

Show All Show All

Change how PivotTable or PivotChart data is summarized

  1. In a PivotTable report, click the data field or a cell in the data area.

    For a PivotChart report, work in the associated PivotTable report.

  2. On the PivotTable toolbar, click Field Settings Button image.
  3. In the Summarize by box, click the summary function you want to use.

    For some types of source data, and for calculated fields and fields with calculated items, you can't change the summary function.

  4. If you want to use a custom calculation, click Options, click the calculation you want in the Show data as list, and then select a Base field and Base item, if these options are available for the calculation you chose, to provide the data for the calculation. The base field should not be the same one you chose in step 1.

    Note  Setting Show data as to Normal turns off custom calculation.

  5. If the report has multiple data fields, repeat these steps for each one that you want to change.
  6. If your source data allows you to change the summary function, you can use more than one summary method for the same field. Drag the field from the PivotTable Field List window to the data area a second time, and then repeat the steps above for the second instance of the field.

Note  When you change the summary method in a PivotChart report or its associated PivotTable report, some chart formatting may be lost.