Show All

Hide All

1. In a PivotTable 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.), click the data field (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.) or a cell in the data area.

   For a PivotChart report (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.), work in the 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.).

2. On the PivotTable toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.), click Field Settings .
3. In the Summarize by box, click 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.) 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.

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