The idea in one sentence
A pivot table is a summary table that groups your rows by one or more categories and then totals, counts or averages the numbers for each group. You choose how to group, and the program does the arithmetic. If you have ever sorted a long list and added up all the entries for each category by hand, a pivot table does that job automatically and lets you rearrange the view in seconds.
The name comes from the ability to “pivot” the layout, swapping what appears down the side and across the top without touching the original data.
What a pivot table needs
Pivot tables work best on a tidy list: a header at the top of each column, one record per row and no blank rows or columns inside the data. Each column should hold one kind of information. If the data is messy, with inconsistent spellings or numbers stored as text, the summary will be messy too. Variants such as “East” and “east “ will show up as separate groups, so some data cleaning beforehand pays off.
A tiny hypothetical example
Imagine a small list of six sales:
| Region | Product | Amount |
|---|---|---|
| East | Pens | 10 |
| East | Notebooks | 20 |
| West | Pens | 15 |
| West | Notebooks | 5 |
| East | Pens | 5 |
| West | Pens | 10 |
The total of all amounts is 10 + 20 + 15 + 5 + 5 + 10 = 65.
Grouping by one category
If you place Region in the rows area and sum the Amount, you get one line per region:
- East: 10 + 20 + 5 = 35
- West: 15 + 5 + 10 = 30
The grand total is 35 + 30 = 65, matching the original list. That check, comparing the pivot total with a plain sum, is a good habit.
Grouping by two categories
Now put Region in the rows and Product in the columns. The table becomes a grid:
| Notebooks | Pens | Total | |
|---|---|---|---|
| East | 20 | 15 | 35 |
| West | 5 | 25 | 30 |
| Total | 25 | 40 | 65 |
You can verify each cell. East pens are 10 + 5 = 15. West pens are 15 + 10 = 25. The column totals are 20 + 5 = 25 for notebooks and 15 + 25 = 40 for pens, and 25 + 40 = 65.
Now the table can answer questions at a glance: pens bring in more than notebooks overall, and the West sells far more pens than notebooks.
The four building blocks
Most pivot table tools use the same four areas, and the names are similar across programs:
- Rows: the categories listed down the side.
- Columns: the categories listed across the top.
- Values: the numbers to summarize, and how to summarize them.
- Filters: a way to limit the whole table to a subset, such as one month or one product.
You build the table by choosing which column of your data goes in each area, often by dragging field names into place.
Choosing how to summarize
Values can be summarized in several ways, and the choice changes the meaning:
- Sum adds up the amounts, as in our example.
- Count counts the number of records. In the example, East has three records and West has three.
- Average takes the mean per record. East’s average is 35 divided by 3, about 11.7, and West’s is 30 divided by 3, exactly 10.
- Minimum and maximum show the smallest and largest values.
Choosing “count” when you meant “sum” is a very common slip. This happens when the values column is treated as text, so check that the summary label says what you intend. The usual cautions about averages apply too; one large value can lift a group’s mean, as discussed under the median average.
Grouping dates and numbers
Pivot tables can also group dates into months, quarters or years, and numbers into ranges, such as ages in bands of ten years. This is helpful for spotting patterns over time or across ranges without adding extra columns. Check how the grouping is defined, since different tools choose different default boundaries.
Keeping the data and summary in step
Many pivot tables do not update automatically when the source data changes. You often need to refresh them. If you add rows below the original list, check that the pivot’s source range includes them; this mirrors the wrong-range problem behind many spreadsheet errors. Using a structured table or a range that expands with new data helps.
Another good habit is to keep the pivot table on its own sheet, away from the raw data, and never to type over its cells. If you need a modified layout, change the fields instead.
When a pivot table helps
- Summarizing sales, expenses or hours by category, person or month.
- Counting how often something occurs, such as responses by answer in a survey.
- Comparing groups, like audience segments, to see which differ.
- Exploring data quickly before deciding which charts or dashboard figures to build.
For a question that needs only one total, a simple function is often easier than a pivot table. Filtering data is also quicker when you merely want to see a subset of rows rather than a summary.
Reading the result carefully
A pivot table shows what the data contains, not why. A group with a large total may simply have more records, so consider counts and averages alongside sums. Small groups can produce unstable averages. If a result seems surprising, drill down to the underlying rows; most tools let you double-click a number to see the records behind it. And remember that pivot tables inherit every problem in the source data, so a quick review of the underlying list is always sensible.