Basic Aggregations and Value Settings

When you add a field to the Values area, Excel applies an aggregation automatically. In this chapter, you’ll learn how these aggregations work and how to change them to get the result you actually need.

In a Pivot Table, the Values area controls calculations. When a numeric field is added, Excel usually applies Sum by default. This default behavior is not always correct. The same data can be summarized in different ways depending on the question you want to answer.

Common aggregation options include:

• Sum: adds all values;
• Count: counts records;
• Average: calculates the mean value;
• Min / Max: shows smallest or largest value.

To change how a field is calculated:
a) Click the dropdown next to the field in the Values area;
b) Open Value Field Settings;

c) Choose the required aggregation.

Pivot Tables also allow multiple value fields at the same time.

You can add the same field more than once and apply different calculations to each version. This makes it possible to compare totals, counts, and averages in a single report.

Adjust the aggregation used in a Pivot Table.

Do the following:

• Create or reuse a Pivot Table;
• Place Category in Rows;
• Place Sales in Values;
• Change the aggregation from Sum to Average;
• Add Sales to Values a second time and keep it as Sum.