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) |
About this data: Teaching sample for a fictional shop “NovaCraft Tools”. Figures and names (e.g. Priya Deshmukh) are made up for this lesson on summarize values by.
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.
Loading practice tasks...
- Total deals won (Easy): Add up Deals won in the "Deals won" column.
- Average avg deal value (Easy): Average of the Avg deal value (£) column, rounded to 2 decimals.
- Rows in North (Easy): How many rows have Region "North"?
- Largest deals won (Easy): The largest value in the "Deals won" column.
- Deals won for North (Medium): Total of the Deals won column where Region is "North".
- North in Glasgow (Medium): Total of the Deals won column where Region is "North" and City is "Glasgow".
- Best in North (Medium): Largest value in the "Avg deal value (£)" column where "Region" is "North".
- Value of row 2 (Medium): Multiply the first-row Deals won by the first-row Avg deal value (£).
- Look up Kavya Reddy (Medium): The Deals won for "Kavya Reddy" (use a lookup on the "Name" column).
- Total value (Hard): Sum of "Deals won" times "Avg deal value (£)" for all rows.
- Share of North (Hard): Share of the "Deals won" column that is "North", rounded to 3 decimals.
- Total "Deals won" for South (Hard): Add up the "Deals won" column for the rows where "Region" is "South".
- Above average? (Hard): "Above" if the "Avg deal value (£)" value in the first row is above the average of the "Avg deal value (£)" column, else "Below".
Worked example — summarize values by
Why
Vikram Rao in Pune and Yuki Tanaka abroad both need a clear, repeatable way to use summarize values by on a HomeNest Supplies sheet without breaking totals.
What
Summarize Values By is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for summarize values by.
- Enter a small made-up value for Vikram Rao using € where money appears.
- Add a second value for Yuki Tanaka using € so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once summarize values by is correct on one row, copy the same pattern down the column.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
