Grouping and Pivot Tables

Lesson 6 · taught by Pip

A single total is useful, but the real questions usually live one level deeper. Not just "what were total sales?" but "what were sales for each region?" That's grouping: sorting your rows into buckets by some category, then summarizing each bucket on its own. The tool that makes this almost effortless is the pivot table.

Think of sorting laundry. You don't weigh the whole heap; you separate it into piles — whites, colors, darks — and deal with each pile. A pivot table sorts your rows into piles by a category you choose, then totals (or averages, or counts) each pile for you, all at once.

What grouping answers

Grouping answers every "per" question — and those are the questions that drive real decisions.

Total sales per region. Average test score per class. Number of visits per month. Headcount per department. Each one takes a long list and folds it into a short, readable summary, one line per category. A thousand-row sales sheet becomes a five-line table: one line per region, each with its total. That short table is something a human can actually act on.

What a pivot table is

A pivot table is a special tool built into every major spreadsheet that does grouping-and-summarizing for you, with no formulas to write. You hand it your data and tell it three things, and it builds the summary instantly.

The name comes from "pivoting" — swiveling your data around to view it from a new angle, the way you'd turn an object in your hand to see a different side. The rows stay the same underneath; you're just choosing a fresh way to look at them.

The three choices you make

Every pivot table boils down to three plain decisions. Get comfortable with these and pivot tables stop being scary.

Rows: what to group by. Which category becomes your piles? Choose "Region," and you get one row per region. This is the bucket.

Values: what to summarize. Which number do you want for each bucket, and how? Choose "Amount" and "Sum," and each region's row shows its total sales. You could pick Average, Count, Max — the same workhorses from the last lesson, now applied per group.

Columns (optional): a second way to split. You can add a second category across the top to make a grid — Region down the side, Month across the top, sales in every cell. Now you can see not just sales per region, but sales per region per month. Powerful, but start with just rows and values until that feels natural.

To build one, you select your clean table, choose "Insert Pivot Table" (the wording varies a little by tool), and drag your chosen field into the Rows box and your number into the Values box. The summary appears in seconds. Change your mind? Drag a different field in, and it rebuilds — that's the pivot, swiveling.

A clean sheet pays off here

Remember lesson two? This is where it earns its keep. If "New York" was spelled three ways, your pivot table makes three separate region piles instead of one — and your per-region totals are quietly wrong. A pivot table is only as honest as the categories underneath it. Clean first, group second.

Why this matters

"Per category" is how businesses and organizations actually think — by product, by store, by month, by team. The pivot table is the single most powerful everyday tool for turning a raw export into the kind of summary a manager, a teacher, or a treasurer can read and decide from. If you learn one "advanced" spreadsheet skill, learn this one.

Your turn

With a sheet that has at least one category column and one number column:

1. Insert a pivot table from your data.
2. Drag the category into Rows (region, type, month — whatever you have).
3. Drag the number into Values, set to Sum. Read your per-category totals.
4. Switch the Values setting from Sum to Average, and watch every figure change meaning.

Next, we'll take a summary like this and turn it into a picture — choosing the right chart for the question.