← ACARA Digital Technologies Years 9–10

Changing a pivot table's value summary

A pivot table option that changes what is calculated for each group, such as Sum, Count or Average. It can also show values as a % of column total and set the number format.

Part of: ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 53

Understanding Changing a pivot table's value summary in ACARA Digital Technologies Years 9–10

What it does: The Show Values As tab with % of Column Total turns each group's value into a percentage of the column's grand total. Number Format inside Value Field Settings sets how the values look, such as Currency, without changing the calculation. Count tallies how many rows are in each group. Sum would add up the numbers in the field instead. Average gives the typical mark per student, so classes of different sizes can be compared fairly.

Common mistakes: Count only tells you how many students are in each house. To get the average points, choose Average in Value Field Settings: Summarise by: Average. Average gives the typical stock per item, not the total. To add up all the stock in each category, use Summarise by: Sum.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Changing a pivot table's value summary to a class →

This module's key terms are also in a printable crossword (Modules 378–412) and word search (Modules 333–412).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A pivot table shows the SUM of Price for each category, but you want the typical price. What should you change in Value Field Settings?
Change the number format to CurrencyChange Summarize Values By from Sum to Average
Matches
Pivot table shows Count of Sales when you wanted the total dollars sold. Fix: Right-click a heading > Sort
↓
Sorting only reorders the groups. To get total dollars you must open Value Field Settings and change Count to Sum.
Applying
Meanings
Term
Value Field Settings
Meaning
A pivot table option that changes what is calculated for each group, such as Sum, Count or Average. It can also show values as a % of column total and set the number format.
Practice
Right-click a value > Value Field Settings
Testing
Gaps
Value Field Settings > Summarize by:
Check Answer
Spots
2Rows: House
3Summarise by: Count

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Arctic Hare' badge — Speed Indexer, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A pivot table shows the SUM of Price for each category, but you want the typical price. What should you change in Value Field Settings?
A. Change Summarize Values By from Sum to Average ✓
B. Change the number format to Currency
Sum, Count and Average are chosen under Summarize Values By. Number format only changes how the numbers look, not how they are calculated.
2. You want to know how many orders each student made. Which summary should the value field use?
A. Count ✓
B. Sum
Count tallies how many rows are in each group. Sum would add up the numbers in the field instead.
3. Which statement is true about Value Field Settings?
A. It changes the colour of the pivot chart bars only
B. It changes what is calculated for each group in the pivot table ✓
Value Field Settings controls the calculation (Sum, Count, Average and more) and the number format of the values.
4. A pivot table of Marks by Class shows Sum. A class with more students looks better only because it has more marks added up. Which summary is fairer?
A. Average ✓
B. Sum
Average gives the typical mark per student, so classes of different sizes can be compared fairly.
5. You want each house's points to show as a share of the whole school's points. Which option do you pick in Show Values As?
A. No Calculation
B. % of Column Total ✓
% of Column Total divides each value by the column's grand total. No Calculation just shows the raw values.

Match the Pairs

Value Field Settings > Summarize Values By > Average → Makes the pivot table show the mean of each group instead of the total
Value Field Settings > Summarize Values By > Count → Makes the pivot table show how many rows are in each group
Show each group as a share of the whole column instead of a raw total → Value Field Settings > Show Values As > % of Column Total
Display the pivot table values as currency with 2 decimal places → Value Field Settings > Number Format > Currency
Pivot table shows Count of Sales when you wanted the total dollars sold. Fix: Right-click a heading > Sort → Sorting only reorders the groups. To get total dollars you must open Value Field Settings and change Count to Sum.
Format Cells on the source data to show % of column total in the pivot table → Formatting the source cells does not change the pivot calculation. Use Show Values As > % of Column Total in Value Field Settings.

Key Term

Value Field Settings
A pivot table option that changes what is calculated for each group, such as Sum, Count or Average. It can also show values as a % of column total and set the number format.

Fill in the Blanks

1. Value Field Settings > Summarize by: Sum
Hint: Type the calculation that adds up all the numbers in each group.
2. Rows: House Values: Points Summarize by: Count
Hint: Type the calculation that counts how many items are in each group instead of adding them.
3. Rows: Subject Values: Mark Summarize by: Average
Hint: Type the calculation that gives the mean mark for each group.
4. Show Values As: % of Column Total
Hint: Type the word for the vertical direction of values, so each value is shown as a share of its field's total.
5. Values: Sum of Price Number Format: Currency
Hint: Type the number format category that shows prices with dollar signs.

Spot the Error

Question 1
A # Goal: average points
B Rows: House
C Summarise by: Count
Line C — Count only tells you how many students are in each house. To get the average points, choose Average in Value Field Settings: Summarise by: Average.
Question 2
A # Goal: total stock
B Rows: Category
C Summarise by: Average
Line C — Average gives the typical stock per item, not the total. To add up all the stock in each category, use Summarise by: Sum.
Question 3
A # Goal: share of total sales
B Rows: Category
C Values: Sales
D Show as: No Calculation
Line D — No Calculation shows the raw sales values, not each category's share. Use Show as: % of Column Total to turn each value into a percentage.
Question 4
A # Goal: average price in $
B Rows: Category
C Summarise by: Average
D Number format: Percentage
Line D — A price should be shown as money. Percentage would show 450% instead of $4.50, so set Number format: Currency.
Question 5
A Click a Values field
B Choose Report Layout
C Pick Summarise by: Average
D Click OK
Line B — Report Layout changes how the pivot table looks, not what it calculates. Choose Value Field Settings to change Sum, Count or Average.

Student with a class code? Join your class →