Grouping dates and numbers in a pivot table

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. How do you start grouping dates in a pivot table?

A Right-click a date in the pivot table, then choose Sort A to Z

B Right-click a date in the pivot table, then choose Group

2. A pivot table lists 300 separate sale dates. What does grouping by Months do?

A It bundles the dates so each month becomes one row with a total

B It deletes the dates and keeps only the first day of each month

3. Your data covers three years. You group the dates by Months only. What can go wrong?

A Excel refuses to group the dates

B January from every year is added into one January row

4. Which choice shows dates grouped by both years and months?

A Select Months and Years in the Grouping box

B Select only Days in the Grouping box

5. Ages in a pivot table are grouped with Starting at 10, Ending at 19 and By 5. Which ranges result?

A 10-14 and 15-19

B 10-12, 13-15 and 16-19

Match the Pairs

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

Left column		Right column
1Right-click a date > Group > Months, Years		ARight-click an Order Date > Group > Months
2Group > Starting at 0, Ending at 100, By 10		BA group size of 0 is not a valid range size. Use a positive number such as 10 so the ranges are equal-sized.
3To summarise sales by month instead of by each day, group the order dates in the pivot table		CPuts numbers into equal-sized ranges of 10 (0-9, 10-19, 20-29 and so on)
4To count students by mark band, group the marks into ranges of equal size		DChoosing only Months mixes January from every year into one row. Select Years as well as Months so each year stays separate.
5Group dates by Months only, across 3 years of data		EBundles daily dates into months and years so the pivot table shows one row per month instead of one row per day
6Group marks: Starting at 0, Ending at 100, By 0		FRight-click a Mark > Group > By 10

Definitions

Group

Fill in the Blanks

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

Right-click a date, then choose _____

Right-click a date > Group > ______

Starting at 0, Ending at 100, __ 10

Marks grouped By __: 0-9, 10-19, 20-29

Group by: Months and _____ Jan 2024 and Jan 2025 stay separate

Spot the Error

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

Question 1

A Insert a pivot table

B Add Date to the Rows area

C Right-click a date, Ungroup

D Tick Months and Years

E Click OK

Question 2

A Add Sales to Values

B Add Date to Rows

C Right-click a date, Group

D Select Hours

E Pivot shows one row per year

Question 3

A Add Order Date to Rows

B Double-click an order date

C Choose Group

D Select Years

Question 4

A Add Date to Rows

B Right-click a date

C Choose Group

D Select Months only

E Click OK

Question 5

A Add Mark to Rows

B Right-click a mark, Group

C Set Starting at 0, By 0

D Ranges: 0-19, 20-39, 40-59

Answer Key

Grouping dates and numbers in a pivot table

Section A: Quick Questions

B — Right-click a date in the pivot table, then choose Group

A — It bundles the dates so each month becomes one row with a total

B — January from every year is added into one January row

A — Select Months and Years in the Grouping box

A — 10-14 and 15-19

Section B: Match the Pairs

Right-click a date > Group > Months, Years → Bundles daily dates into months and years so the pivot table shows one row per month instead of one row per day

Group > Starting at 0, Ending at 100, By 10 → Puts numbers into equal-sized ranges of 10 (0-9, 10-19, 20-29 and so on)

To summarise sales by month instead of by each day, group the order dates in the pivot table → Right-click an Order Date > Group > Months

To count students by mark band, group the marks into ranges of equal size → Right-click a Mark > Group > By 10

Group dates by Months only, across 3 years of data → Choosing only Months mixes January from every year into one row. Select Years as well as Months so each year stays separate.

Group marks: Starting at 0, Ending at 100, By 0 → A group size of 0 is not a valid range size. Use a positive number such as 10 so the ranges are equal-sized.

Section C: Definitions

Group — A pivot table feature that bundles dates into months or years, or numbers into equal-sized ranges, so the summary shows useful chunks. Right-click a value, then choose Group.

Section D: Fill in the Blanks

Right-click a date, then choose Group

Right-click a date > Group > Months

Starting at 0, Ending at 100, By 10

Marks grouped By 10: 0-9, 10-19, 20-29

Group by: Months and Years
Jan 2024 and Jan 2025 stay separate

Section E: Spot the Error

C — Ungroup would split grouped items back apart. To bundle dates into months and years you choose Group, so the line should be 'Right-click a date, Group'.

D — The goal is one row per year, so you must select Years in the Group box. Selecting Hours would split the dates into hours instead.

B — Double-clicking a value drills down into its details instead of opening the menu. You need to right-click the date to find Group.

D — Selecting Months only would add January 2024 and January 2025 into one row. Select Months and Years so each month stays within its own year.

C — The By box sets the size of each equal range, so it cannot be 0. To get ranges of 20 marks, it should be By 20.