Excel for Class Projects: PivotTables and analysis

01

From project question to analysis-ready data

In simple words

A PivotTable is like a sorting machine for a big box of receipts. You tell it "group by team" or "group by month", and it adds everything up for you in a second. But the machine only works if every receipt is written the same way.

Before this course: Excel for Coursework: tables, charts and lab reports (course 13.2.2). You should already know how to lay out clean data as an Excel table, use SUMIF, COUNTIF and AVERAGEIF, calculate basic statistics, and make a chart with a title and labelled axes.

Key facts

Start with a question you can answer with numbers, for example "Which team raised the most money, and did sales grow from February to March?"
Microsoft Support's data tips for PivotTables: every column needs a header, with a single row of unique, non-blank labels; the data should be in a tabular format with no blank rows or columns.
Microsoft recommends using an Excel table as the source: rows you add to a table are included automatically the next time you refresh the PivotTable.
Keep one kind of value per column: real dates in a Date column, numbers (no units typed in) in number columns, and the same spelling for each category ("Red", never "red " or "RED").
Before you build a PivotTable, know one or two answers you can check another way. Two functions help:
COUNTIFS counts the rows where all conditions are met. Syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]...). Each extra range must have the same number of rows and columns as the first one. Up to 127 range/criteria pairs are allowed.
SUMIFS adds values that meet several conditions. Syntax: SUMIFS(sum_range, criteria_range1, criteria1, ...). Microsoft points out that the order differs from SUMIF: in SUMIFS the sum_range comes first; in SUMIF it comes third.

Example (made-up practice data, checked in Python)

A class fundraiser log with 12 rows. Prices: Cookies $2, Juice $1, Bracelets $3.

Date	Team	Item	Qty	Sales ($)
2026-02-03	Red	Cookies	10	20
2026-02-05	Blue	Juice	12	12
2026-02-10	Green	Bracelets	4	12
2026-02-12	Red	Bracelets	6	18
2026-02-17	Blue	Cookies	8	16
2026-02-24	Green	Cookies	5	10
2026-03-03	Red	Juice	15	15
2026-03-05	Blue	Bracelets	7	21
2026-03-10	Green	Juice	9	9
2026-03-12	Red	Cookies	12	24
2026-03-17	Blue	Cookies	6	12
2026-03-24	Green	Bracelets	3	9

Total sales are $178 and total quantity is 97. Two check answers: =COUNTIFS(B2:B13,"Red",C2:C13,"Cookies") gives 2 rows, and =SUMIFS(E2:E13,B2:B13,"Red",C2:C13,"Cookies") gives $44.

Step by step

Write your project question at the top of a new sheet called Notes.
On a sheet called Data, type the 12 rows above (or your own project data) with the headers Date, Team, Item, Qty, Sales ($).
Click any cell in the data and choose Insert, then Table. Tick "My table has headers" and click OK.
Rename the table on the Table Design tab (Table Name box), for example Sales.
In an empty cell type =COUNTIFS(B2:B13,"Red",C2:C13,"Cookies"). Check: 2.
In the next cell type =SUMIFS(E2:E13,B2:B13,"Red",C2:C13,"Cookies"). Check: 44.
Scan each category column for spelling differences and fix them in the data.
Check: one header row, no blank rows or columns, and two "check answers" written in your Notes sheet.

(Menu names can differ by version.)

LESSON 01From project question to analysis-ready data

LESSON 01 / ANIMATED LESSON / AI VOICE00:00 / 01:00

Voice

Read the video script

[Host] "A PivotTable can add up thousands of rows in a second. But only if your data is tidy."
[Screen: table with headers Date, Team, Item, Qty, Sales] "One header row. No blank rows. Same spelling every time."
[Screen: =SUMIFS(E2:E13,B2:B13,"Red",C2:C13,"Cookies")] "Before you pivot, work out one answer with SUMIFS. Red team, cookies: forty-four dollars."
[Text on screen: SUMIFS puts sum_range FIRST] "Careful: in SUMIFS, the range you add comes first."

Try it now

Write a one-sentence question for a class project you care about (sports scores, reading log, a short survey). Set up at least 12 rows of data as a table and write two check answers with COUNTIFS and SUMIFS.

ClaudeStuck? Copy a ready-made prompt for Claude

Copy this, open Claude, paste it, and Claude will walk you through the lesson step by step.

For a grown-up: Claude is for adults (18+). Copy this, paste it into Claude, and use the answer to explain the lesson to your child.

I'm learning "Excel for Class Projects: PivotTables and analysis" on The Future Market. Lesson 1: "From project question to analysis-ready data".

What the lesson teaches:
- Start with a question you can answer with numbers, for example "Which team raised the most money, and did sales grow from February to March?"
- Microsoft Support's data tips for PivotTables: every column needs a header, with a single row of unique, non-blank labels; the data should be in a tabular format with no blank rows or columns.
- Microsoft recommends using an Excel table as the source: rows you add to a table are included automatically the next time you refresh the PivotTable.
- Keep one kind of value per column: real dates in a Date column, numbers (no units typed in) in number columns, and the same spelling for each category ("Red", never "red " or "RED").
- Before you build a PivotTable, know one or two answers you can check another way. Two functions help:
- COUNTIFS counts the rows where all conditions are met. Syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]...). Each extra range must have the same number of rows and columns as the first one. Up to 127 range/criteria pairs are allowed.
- SUMIFS adds values that meet several conditions. Syntax: SUMIFS(sum_range, criteria_range1, criteria1, ...). Microsoft points out that the order differs from SUMIF: in SUMIFS the sum_range comes first; in SUMIF it comes third.

The exercise I'm working on:
Write a one-sentence question for a class project you care about (sports scores, reading log, a short survey). Set up at least 12 rows of data as a table and write two check answers with COUNTIFS and SUMIFS.

I'm stuck. Please:
1. Explain the main idea in plain, simple words with one everyday example.
2. Walk me through the exercise one small step at a time. Ask me what I've done so far before giving the next step.
3. Don't just give me the final answer. Help me do it myself, and check my work when I share it.

I'm a parent helping my child (about 8 years old) with a lesson called "From project question to analysis-ready data" from the course "Excel for Class Projects: PivotTables and analysis".

Here's what the lesson says:
A PivotTable is like a sorting machine for a big box of receipts. You tell it "group by team" or "group by month", and it adds everything up for you in a second. But the machine only works if every receipt is written the same way.

The activity:
Write a one-sentence question for a class project you care about (sports scores, reading log, a short survey). Set up at least 12 rows of data as a table and write two check answers with COUNTIFS and SUMIFS.

Please explain this so an 8-year-old understands: short sentences, a fun everyday example, and no hard words.
Then give me 3 simple questions I can ask my child to check they understood, and a way to do the activity together, one tiny step at a time.

Open Claude

Sources

Microsoft Support, Create a PivotTable to analyze worksheet data: support.microsoft.com/office/18fb0032-b01a-4c99-9a5f-7ab09edde05a
Microsoft Support, COUNTIFS function: support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4ae
Microsoft Support, SUMIFS function: support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-

My notes

I learned this

02