Changing a pivot table's value summary

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

2. You want to know how many orders each student made. Which summary should the value field use?

A Count

B Sum

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

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

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

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

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

Definitions

Value Field Settings

Fill in the Blanks

Complete each line of code by filling in the missing parts.

Value Field Settings > Summarize by: ___

Rows: House Values: Points Summarize by: _____

Rows: Subject Values: Mark Summarize by: _______

Show Values As: % of ______ Total

Values: Sum of Price Number Format: ________

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A # Goal: average points

B Rows: House

C Summarise by: Count

Question 2

A # Goal: total stock

B Rows: Category

C Summarise by: Average

Question 3

A # Goal: share of total sales

B Rows: Category

C Values: Sales

D Show as: No Calculation

Question 4

A # Goal: average price in $

B Rows: Category

C Summarise by: Average

D Number format: Percentage

Question 5

A Click a Values field

B Choose Report Layout

C Pick Summarise by: Average

D Click OK

Answer Key

Changing a pivot table's value summary

Section A: Quick Questions

A — Change Summarize Values By from Sum to Average

A — Count

B — It changes what is calculated for each group in the pivot table

A — Average

B — % of Column Total

Section B: 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.

Section C: Definitions

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.

Section D: Fill in the Blanks

Value Field Settings > Summarize by: Sum

Rows: House
Values: Points
Summarize by: Count

Rows: Subject
Values: Mark
Summarize by: Average

Show Values As: % of Column Total

Values: Sum of Price
Number Format: Currency

Section E: Spot the Error

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.

C — Average gives the typical stock per item, not the total. To add up all the stock in each category, use Summarise by: Sum.

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.

D — A price should be shown as money. Percentage would show 450% instead of $4.50, so set Number format: Currency.

B — Report Layout changes how the pivot table looks, not what it calculates. Choose Value Field Settings to change Sum, Count or Average.