← VCE Spreadsheets, SQL & Visualisation

Pivot tables

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.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 35, VCE Data Analytics - U3O1 - Data Analytics — Module 107, VCE Data Analytics - U4O1 - Development and Evaluation — Module 13, 9-10 Digital Technologies: Analysing and visualising data — Module 9, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 114, ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 51

Understanding Pivot tables in VCE Spreadsheets, SQL & Visualisation

What it does: Putting Month in the Columns area makes each month its own column in the pivot table. A pivot table does not update by itself, so you refresh it to bring in new or edited data. Row and column fields are used to group data. The Values area is for the numbers you calculate, such as a sum. Count tells you how many records are in each group. Sum would add up numbers, which does not make sense for sport names. A pivot table is a summary view. Your original records stay unchanged. Pivot tables summarise large amounts of data by group, which makes them great for totals, counts and averages.

Common mistakes: A sum needs a number field. Region is text, so it can't be added up. The line should be Values: SUM of Amount. 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.

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 Pivot tables to a class →

This module's key terms are also in a printable crossword (Modules 35–44) and word search (Modules 35–54).

Take this worksheet further — free with Cloodit

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

Learning

Duo

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

A pivot table that groups by food item and sums the salesA pivot table that copies every record onto a new sheet

Matches

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.

Applying

Meanings

Term

pivot table

Meaning

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.

Practice

Rows: Subject

Testing

Gaps

: Region

Check Answer

Spots

3Columns: Month

4Values: SUM of Region

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

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 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 ✓

A pivot table groups records under a field (like food item) and calculates a total for each group.

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

Row and column fields are used to group data. The Values area is for the numbers you calculate, such as a sum.

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

A. Sum

B. Count ✓

Count tells you how many records are in each group. Sum would add up numbers, which does not make sense for sport names.

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

A. Average ✓

B. Count

Average works out the mean mark for each group. Count only tells you how many marks there are.

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

Row and column fields are the groups. The Values field is what gets summed, counted or averaged.

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.

Key Term

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.

Fill in the Blanks

1. Rows: Region Values: SUM of Sales

Hint: Type the pivot table area that lists each group down the left side.

2. Rows: Region Columns: Month Values: COUNT of Orders

Hint: Type the pivot table area that spreads groups across the top.

3. Rows: Year Level Values: SUM of Canteen Sales

Hint: Type the calculation that adds up the amounts in each group.

4. Rows: Class Values: AVERAGE of Test Mark

Hint: Type the calculation that finds the mean for each group.

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

Hint: Type the action you use so the pivot table matches changed data.

Spot the Error

Question 1

A Source: Sales!A1:D200

B Rows: Region

C Columns: Month

D Values: SUM of Region

Line 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.

Question 2

A Source: Canteen!A1:C150

B Rows: SUM of Item

C Values: SUM of Quantity

Line 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.

Question 3

A Goal: average score per class

B Source: Marks!A1:D90

C Rows: Score

D Values: AVERAGE of Score

Line 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.

Question 4

A Goal: count orders per item

B Source: Orders!A1:C300

C Rows: Item

D Values: SUM of Order ID

Line 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.

Question 5

A Edit: add 20 new rows to Sales

B Pivot: Press Ctrl+S to update

C Result: totals now updated

Line 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.

Student with a class code? Join your class →