Pivot tables

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A school canteen has 2,000 sales records. Which tool quickly shows total sales for each food item?

A A pivot table that copies every record onto a new sheet

B A pivot table that groups by food item and sums the sales

2. In a pivot table showing sales by day of the week, where would you put the field 'Day'?

A In the Rows area, so each day gets its own row

B In the Values area, so it is added up like a number

3. You want to know HOW MANY students chose each sport. Which value setting should you use?

A Sum

B Count

4. You want the typical test mark for each class. Which value setting fits best?

A Average

B Count

5. Which statement about row and column fields is true?

A Rows and columns create a grid, with one calculated value in each cell where a row and column meet

B Row and column fields are the numbers that get added up in the pivot table

Match the Pairs

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

Left column		Right column
1Rows: Region		AAdds up the sales for each group in the pivot table
2Values: Sum of Sales		BThe pivot table was not refreshed, so it still shows the old totals from before the data changed.
3A column field spreads the groups across the top, such as one column for each month		CRight-click > Refresh
4After you change the source data, update the pivot table so its totals are correct		DCount only tells you how many records there are. To get the average mark, use Average of Mark.
5Values: Count of Student Name, to find the average mark		EColumns: Month
6Edit the sales data, then read the old pivot table totals		FPuts each region on its own row so the data is grouped by region

Definitions

pivot table

Fill in the Blanks

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

____: Region Values: SUM of Sales

Rows: Region _______: Month Values: COUNT of Orders

Rows: Year Level Values: ___ of Canteen Sales

Rows: Class Values: _______ of Test Mark

New rows added to the data table. Click _______ to update the pivot table.

Spot the Error

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

Question 1

A Source: Sales!A1:D200

B Rows: Region

C Columns: Month

D Values: SUM of Region

Question 2

A Source: Canteen!A1:C150

B Rows: SUM of Item

C Values: SUM of Quantity

Question 3

A Goal: average score per class

B Source: Marks!A1:D90

C Rows: Score

D Values: AVERAGE of Score

Question 4

A Goal: count orders per item

B Source: Orders!A1:C300

C Rows: Item

D Values: SUM of Order ID

Question 5

A Edit: add 20 new rows to Sales

B Pivot: Press Ctrl+S to update

C Result: totals now updated

Answer Key

Pivot tables

Section A: Quick Questions

B — A pivot table that groups by food item and sums the sales

A — In the Rows area, so each day gets its own row

B — Count

A — Average

A — Rows and columns create a grid, with one calculated value in each cell where a row and column meet

Section B: Match the Pairs

Rows: Region → Puts each region on its own row so the data is grouped by region

Values: Sum of Sales → Adds up the sales for each group in the pivot table

A column field spreads the groups across the top, such as one column for each month → Columns: Month

After you change the source data, update the pivot table so its totals are correct → Right-click > Refresh

Values: Count of Student Name, to find the average mark → Count only tells you how many records there are. To get the average mark, use Average of Mark.

Edit the sales data, then read the old pivot table totals → The pivot table was not refreshed, so it still shows the old totals from before the data changed.

Section C: Definitions

pivot table — A tool that summarises a big table by grouping rows under row and column fields, then working out a sum, count or average for each group. Refresh it when the data changes.

Section D: Fill in the Blanks

Rows: Region
Values: SUM of Sales

Rows: Region
Columns: Month
Values: COUNT of Orders

Rows: Year Level
Values: SUM of Canteen Sales

Rows: Class
Values: AVERAGE of Test Mark

New rows added to the data table.
Click Refresh to update the pivot table.

Section E: Spot the Error

D — A sum needs a number field. Region is text, so it can't be added up. The line should be Values: SUM of Amount.

B — A row field is a column used to group the records, such as Item. It is not a calculation. The line should be Rows: Item. The sum belongs under Values.

C — The goal is one average for each class, so the rows must group by Class. Grouping by Score would make one row for every different score. The line should be Rows: Class.

D — The goal is to count the orders. Adding up ID numbers gives a meaningless total. The line should be Values: COUNT of Order ID.

B — Saving the file does not update a pivot table. After the data changes you must refresh the pivot table, for example by right-clicking and choosing Refresh.