Summarizing Pivot Table Data in Excel

In this post, you’ll learn about summarizing Pivot table data in your Microsoft Excel spreadsheet.

Summarizing Pivot Table Data in Excel

Excel offers you a variety of methods to summarize your data in the Pivot table. Some of its summarize functions are Sum, Count, and Average.

Whenever you place a field in the ∑ Values in the field list pane, it automatically sums the value in the column. The default function for the numeric value fields in the PivotTable is the Sum function, but you can choose a different summary function.

  • In the PivotTable, right-click the Grand Total field, and then click Summarize Values By.
  • From the drop-down menu, click on the function that is necessary.

Summary options and its Functions

Here is the list of summarize options available along with its function.

Summary Options Functions
SumThe normal addition function. It sums up the values present in the column. It’s the default function for value fields that have numeric values. When the sum function is applied and any blank or non-numeric values are changed to 0 in the PivotTable so they can be summed.
CountIt displays the number of nonempty values. It is used by default for value fields that have non-numeric values or blanks.
AverageDisplays the average of the given values.
MaxDisplays the largest value.
MinDisplays the smallest value.
ProductDisplays the product of the values.
StDevAn estimate of the standard deviation of a population, where the sample is a subset of the entire population.
StDevpThe standard deviation of a population, where the population is all of the data to be summarized.
VarIt displays the estimate of the variance of a population, where the sample is a subset of the entire population.
VarpIt displays the variance of a population, where the population is all of the data to be summarized.
Distinct CountIt displays the number of unique values. This summary function only works when you use the Data Model in Excel.

Let’s see the summarize functions with an example.

Take a look at the following Pivot table.

Summarizing Pivot Table Data in Excel
  • In the Grand Total value of Sum of cost price column, right-click and click on the “summarize value by” option
Summarizing Pivot Table Data in Excel
  • In the drop-down menu, choose the average value option.
Summarizing Pivot Table Data in Excel
  • The table immediately displays the average cost price of the products.
Summarizing Pivot Table Data in Excel
  • If you choose the “Max” option, it displays the maximum value present in the column.
Summarizing Pivot Table Data in Excel

You can repeat the steps for other options too.