7. PivotTables and PivotCharts
7.3 Summarize Values By
Right-click a value › Summarize Values By › Sum, Count, Average, Max, Min, Product, More Options… (or Value Field Settings).
| Need | Put in Values | Summarize by |
|---|---|---|
| Sales | Amount | Sum |
| Number of order lines | Order ID | Count |
| Average delivery time | Delivery Mins | Average |
| Slowest delivery | Delivery Mins | Max |
| Number of distinct customers | Customer | Distinct Count (only when the pivot uses the Data Model) |
You can drag the same field twice into Values – e.g. Amount as Sum and Amount as Average (AOV per line).
Worked example. Rows: City; Values: Sum of Amount, Count of Order ID, Average of Delivery Mins → for Pune: ₹544, 4 orders, 11 mins.
Ravindra Bagale's Tip
If a number column has even one text/blank cell, the PivotTable takes Count by default, not Sum – many students read "Count of Amount" as sales! When you drop a field into Values, look at the heading: does it say "Sum of…"? If not, clean the text-numbers in the source (5.6).
Ravindra Bagale's Tip – मराठी
Number column मध्ये एक जरी text/blank cell असली तर PivotTable default Count घेते, Sum नाही – बरेच students "Count of Amount" ला sales समजतात! Values मध्ये field टाकलं की heading बघा: "Sum of…" आहे का? नसेल तर source मधले text-numbers clean करा (5.6).
Ravindra Bagale's Tip – हिंदी
Number column में एक भी text/blank cell हो तो PivotTable default में Count लेती है, Sum नहीं – बहुत से students "Count of Amount" को sales समझ लेते हैं! Values में field डालते ही heading देखो: "Sum of…" है क्या? नहीं है तो source के text-numbers clean करो (5.6).
Practice task
For each city show: total sales, number of orders, average and maximum delivery time, and (with the Data Model) the distinct count of customers.