Stop 02 · Excel & SpreadsheetsTopics · 3/10

PivotTables & PivotCharts

In short

"Break this down by region and month" is the most common request you'll get. A PivotTable answers it in under a minute, with no formulas at all.

A drag-and-drop way to summarize large datasets — grouping, aggregating and cross-tabulating without writing a single formula, and the fastest way to answer 'break this down by X' questions.

The jaadu sorts a messy heap of colourful receipts into neat coloured trays.

Think of it like this

Sorting the hostel bill pile

Imagine a heap of 500 canteen receipts. You sort them into piles by student, then add up each pile. Now re-sort by month instead. A PivotTable does that sorting and adding instantly, and you can re-pile by any column with a drag.

Watch it happen

From 5,000 rows to one small table

Raw rows

Rows: Region

Cols: Month

Values: Sum

PivotChart

1/5You start with 5,000 order rows: date, region, product, amount.

The big ideas

What you need to know

Drag, don't type

Rows, Columns, Values and Filters are four drop zones. Rearranging them gives a new view in seconds.

Change the maths

Values can show Sum, Count, Average, Max or % of total. Count is great for 'how many orders'.

Refresh after new data

Pivots don't update automatically when source data changes. Right-click and Refresh.

Use a Table as the source

Format your data as a Table (Ctrl+T) so new rows are picked up when you refresh.

Save yourself the pain

Common mistakes

Oops

Building the pivot on a range with blank or merged header cells.

Do this instead

Give every column a unique header first. Pivots need clean, rectangular data.

Oops

Forgetting to refresh and presenting last week's numbers.

Do this instead

Refresh before you share. Better, use a Table source and refresh as a habit.

Your mission

Pivot a real dataset

~25 min

Quick check

You want the number of orders per city, not the total amount. What do you change?

Go deeper

1Build a PivotTable summarizing a sample sales dataset by region and month.
2Practice adding calculated fields inside a PivotTable.
3Turn your PivotTable into a PivotChart and note how it updates automatically.