Pivot table

A pivot table summarizes a long table in seconds: it groups by the columns you pick and sums, counts or averages another, with no formulas.

Select any words here to ask your Maestro about them.

In the pivot table, Downtown sold $9,660 this month and North $6,120. Does Downtown also have the highest average sale per receipt?

One month of sales, one row per receipt. Example data.

What it's for: answering questions like how much each branch sold, every month, in seconds and without writing a formula. You group by dragging columns into Rows and Columns, and choose what to calculate in Values: sum, count, average, max or min.

Before you trust it: check what each row stands for (the table's grain) and what the field in Values says. If an Amount cell is blank or holds text, Excel switches to Count instead of Sum, and the table counts rows.

A pivot table that counts values is a frequency table. In a database, SQL does the same with GROUP BY.

Practice it in How do you calculate the average ticket when one ticket spans several rows?. SQL for Data People turns pivot tables into GROUP BY.

How it's used

How to make it in Excel

Columns: Date, Branch, Receipt, Amount

Click any cell in your data › Insert › PivotTable › OK.Excel takes the whole range and opens a new sheet with the field list.
Drag Branch to Rows.One row per branch, no repeats.
marked: Drag Amount to Values.Sum of Amount appears: what each branch sold.
Click Sum of Amount › Value Field Settings › Average.Same field, different calculation. Count, Max and Min are there too.
If your data spans several months, drag Date to Columns: Excel groups it by month.

If your data will grow, turn it into a table first (Insert › Table). Then Refresh picks up the new rows.

When to look closer

When each row is an item, not a receipt

January: 2,460 rows, one per item

marked: Average of Amount: $13

Your sheet has 2,460 rows, one per item sold. Is the pivot table's average what a customer spends?

Ask your Maestro about Pivot table

It answers from the free course page closest to this word, the way it answers while you study.

Example

You: What does "Pivot table" mean, in plain words?

Your Maestro: I'll explain it with a case of yours: it sticks better than a definition. Which question at work would you answer with a pivot table?

Asking about: Answers from the page "How do I check a table's rows, columns and data types in pandas?"

Only this browser keeps what you ask here. How it works

Learn more

Create a PivotTable, in Microsoft Support

Frequency table
A frequency table counts how often each value shows up (absolute frequency). Relative frequency is that count over the total: 5 of 10 = 50%.
Mean (average)
To find the mean (the average), add up all the values and divide by how many there are: 15, 7, 8, 13 and 7 add up to 50, and 50 ÷ 5 = 10.
Grain (of a table)
What one row of a table stands for: a person, a transaction, a planet, a person per language. Counting rows counts what you want only if you know the grain.
Database
A database stores data in connected tables, with rules for each column, so many people and programs can read and change it without stepping on each other.

The whole glossary