Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
When you wish to show different aggregation on your Pivot Table fields than what is provided by default.
Understanding and changing PivotTable field aggregation.
Where/when to use the technique
When you wish to show different aggregation on your PivotTable fields than what is provided by default.
Default Aggregation for fields dragged into the values area of a PivotTable
- Columns with purely numeric data will automatically aggregate as a Sum
- Columns with any text in them will automatically be aggregated as a Count
- There are 3 options to change the aggregation type
- Right click the column in the PivotTable > Value Field Settings
- Go to the VALUES area of the PivotTable field list > click the drop down arrow on the field > Value Field Settings
- Right click the column in the PivotTable > Summarize Values By
Issues where numeric data aggregates as Count (instead of Sum) by default
- This issue occurs when the source column contains:
- Text values
- To fix this issue
- Check the source column for any text/blank values
- Replace text with true values
- Replace blanks with 0
- Refresh the PivotTable
Aggregation Types Available
- Sum, Count, Count Numbers, Average, Max, Min
- Product, StdDev, StdDevp
- Var, Varp
Lesson notes are only available for subscribers.