What a pivot table does
A pivot table takes a long list of records and summarizes it by category. Imagine a list of several hundred rows, each one a sale with a date, a region, a product and an amount. You may want to know total sales per region, or sales per product per month. You could write formulas for each combination, but a pivot table builds the whole summary by dragging fields into place, and rebuilds it in seconds when you change your mind.
The key idea is that your original data is left alone. The pivot table is a separate view that reads from it and groups it in whatever way you choose.
Check that your data is ready
Pivot tables work best on a clean, flat table: a single header row, one row per record, one kind of fact per column, and no blank rows or columns or merged cells in the range. Mixed formats cause trouble. If a column of amounts contains some numbers stored as text, those rows may be counted instead of summed, or left out. The preparation described in data cleaning will save you a lot of confusion here.
The four areas
Most tools show a panel with your column headers and four boxes. Dragging a header into a box decides what the table does.
Rows. The categories listed down the left side. Put “Region” here and you get one line per region.
Columns. The categories listed across the top. Put “Month” here and each month becomes a column. Leave this empty for a simple one-dimensional summary.
Values. The number to calculate for each group, and how. Common choices are sum, count, average, minimum and maximum. Sum of Amount gives total sales; count of Order ID gives the number of orders.
Filters. A way to limit the whole table, for example to a single year or one product line.
A small hypothetical example
Suppose a list has six sales rows:
| Region | Product | Amount |
|---|---|---|
| North | Pens | 100 |
| North | Paper | 60 |
| South | Pens | 80 |
| South | Paper | 40 |
| North | Pens | 20 |
| South | Pens | 50 |
The numbers are invented. Put Region in Rows and the sum of Amount in Values. North is 100 + 60 + 20 = 180. South is 80 + 40 + 50 = 170. The grand total is 350, which matches adding all six rows: 100 + 60 + 80 + 40 + 20 + 50 = 350.
Now put Product in Columns. North Pens is 100 + 20 = 120 and North Paper is 60. South Pens is 80 + 50 = 130 and South Paper is 40. Each row total still matches: 120 + 60 = 180, and 130 + 40 = 170. You rearranged the view without touching the data.
Sum versus count
A frequent confusion is choosing the wrong calculation. If you want to know how many requests came in per category, use count. If you want the total hours spent per category, use sum of hours. Averages can mislead when groups are very different in size: an average over two entries and an average over two hundred are not equally reliable. Showing a count next to the average is a useful check.
Grouping dates and numbers
Many tools can group dates automatically into months, quarters or years, and numbers into ranges such as 0 to 9, 10 to 19 and so on. This is convenient for trends and for turning a long list of dates into a readable summary. Check that the grouping matches your needs: a fiscal year may not match a calendar year, and a week may start on different days in different places.
Keeping a pivot table up to date
Most pivot tables do not update on their own when the source changes. You usually need to refresh them, and some tools update only when you ask. Two practical habits help:
- Define the source as a whole table or a range that expands when rows are added. Otherwise new rows may sit outside the pivot’s range and be silently excluded.
- Put a “last refreshed” reminder near the output if others rely on it.
If you plan to build a one-page view for a team, a pivot table is often the calculation layer behind it, and the same discipline applies as in team dashboards.
Common mistakes
- Numbers counted as text. The summary shows counts where you expected sums. Convert the column to numbers and refresh.
- Inconsistent categories. “North” and “north “ show up as two lines. Clean the source.
- Blank headers. Every column in the source needs a name.
- Typing over the output. Cells inside a pivot are generated; changes are lost on refresh. Copy the results elsewhere as values if you want to annotate them.
- Double-counting. If the source has duplicate rows, the totals will be inflated. Check the row count against what you expect.
- Trusting it without a check. Compare the grand total with a simple sum of the source column.
Pivot tables versus formulas
Formulas are better when you need a specific figure in a fixed place, such as a single monthly total that feeds a report. Pivot tables are better for exploring, when you do not yet know which cuts of the data will be useful. Many people use both: a pivot to explore, then spreadsheet formulas to produce the stable figures they report. If you find yourself rebuilding the same pivot every week, that is a sign the task may be a candidate for automation.
Sharing the result
A pivot table can expose more detail than you intend, since a viewer can sometimes expand a summary to see the underlying rows. When sharing, consider whether to send values only, a picture or a protected copy, especially if the data includes personal or confidential information.