Show All

Hide All

1. In the DATA area of the layout diagram, double-click the field for which you want to change the summary function (summary function: A type of calculation that combines source data in a PivotTable report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count, and Average.).
2. In the Summarize by box, click the summary function you want to use.

   For some types of source data (source data: The list or table that's used to create a PivotTable or PivotChart report. Source data can be taken from an Excel list or range, an external database or cube, or another PivotTable report.), and for calculated fields (calculated field: A field in a PivotTable report or PivotChart report that uses a formula you create. Calculated fields can perform calculations by using the contents of other fields in the PivotTable report or PivotChart report.) and fields with calculated items (calculated item: An item within a PivotTable field or PivotChart field that uses a formula you create. Calculated items can perform calculations by using the contents of other items within the same field of the PivotTable report or PivotChart report.), you can't change the summary function.

3. If you want to use a custom calculation (custom calculation: A method of summarizing values in the data area of a PivotTable report by using the values in other cells in the data area. Use the Show data as list on the PivotTable Field dialog for a data field to create custom calculations.), 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 field you chose in step 1.
4. If the report has multiple data fields (data field: A field from a source list, table, or database that contains data that is summarized in a PivotTable report or PivotChart report. A data field usually contains numeric data, such as statistics or sales amounts.), repeat the previous steps for each one that you want to change.
5. 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 group of field buttons on the right 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 (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) or its associated PivotTable report (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.), some chart formatting may be lost.