What Is a Pivot Table? Rows, Columns, Values, and Aggregation Explained

A pivot table is a summary table built from a flat list of records. You pick one field whose values become the output rows, optionally a second field whose values spread across the output columns, and a third field whose numbers get combined in each cell by an aggregation such as sum or average. Microsoft describes a PivotTable as a tool to calculate, summarize, and analyze data so you can see comparisons, patterns, and trends, and that is exactly the job: take hundreds of raw rows and answer a question like "how much did each region sell per month" in one small grid. You can build one from any CSV, right in your browser, with the Pivot CSV tool.

Start with a flat CSV

Everything begins with a flat file: one record per row, one field per column, no summaries anywhere. Here is a small sales log. Each row is a single sale with the month it happened, the region it came from, and the amount.

month,region,amount
Jan,North,1200
Jan,South,800
Jan,North,450
Feb,North,900
Feb,South,1100
Feb,South,300
Mar,North,700
Mar,South,950
Mar,North,150
Mar,South,600

Ten rows is easy to eyeball. Ten thousand is not, and the question you actually care about, "which region is growing?", is invisible in the raw list because the relevant rows are scattered. A pivot table regroups them.

The three roles: rows, columns, values

Pivoting means assigning your fields to three roles.

• Rows: the field whose distinct values label the output rows. Choose region and you get one output row per region: North and South.
• Columns: the field whose distinct values fan out as output columns. Choose month and you get a Jan, a Feb, and a Mar column.
• Values: the field whose entries are collected into each cell and reduced to one number by an aggregation. Here that is amount, aggregated with sum.

Each cell of the result answers one precise question. The cell at row North, column Jan holds the sum of amount for every input row where region is North and month is Jan. There are two such rows, 1200 and 450, so the cell reads 1650. Do that for every combination and you get this.

Ten scattered rows became six cells, and the story jumps out: North started strong in January and faded, while South grew every month. That is the whole trick. A pivot table does not change your data, it changes its shape so a two-dimensional question gets a two-dimensional answer.

Choosing an aggregation

The aggregation decides how the values collected into a cell collapse into one number. Each one answers a different question.

• count: how many rows fell into the cell. It ignores the value field entirely, so it works on text columns too. Use it for frequencies: North made 2 sales in January, South made 1.
• sum: the total. The default choice for money, quantities, and anything else where adding makes sense.
• avg: the mean of the numeric values in the cell. Good for rates and typical sizes, but watch two traps. Blank or non-numeric entries are skipped rather than counted as zero, which quietly shrinks the denominator, and an average of a column that is itself an average (like a percentage) weights every row equally regardless of how much each row represents.
• min and max: the extremes. Use them to find the smallest or largest single sale per group, or the earliest and latest value when the column sorts naturally.
• first: the first value encountered for the group, untouched. Handy for carrying a label along, such as picking one representative product name per region.

Long versus wide data

The input and output above are the two classic shapes of tabular data. The flat CSV is long data: every observation is its own row, and month is a value inside a column. The pivot result is wide data: months have been promoted to column headers, and each row packs several observations. Hadley Wickham's tidy data paper formalizes the long shape as the one where each variable is a column and each observation is a row, and argues it is the right shape for storage and processing precisely because tools can rely on it. Wide is the right shape for reading, because humans compare across a grid faster than down a list. A pivot table is simply the machine that converts long to wide at the moment you need to look, while your source file stays long.

Pivot table or SQL GROUP BY?

If you know SQL, a pivot will feel familiar: SELECT region, SUM(amount) FROM sales GROUP BY region is exactly a pivot with a rows field and no columns field. The difference is dimensionality. GROUP BY puts every grouping combination on its own output row, so grouping by region and month gives you six rows of region, month, total: a long summary. A pivot crosses the two fields, one along the rows and one along the columns, producing the compact grid above. Use GROUP BY output when the result feeds another program, and a pivot when the result feeds a person. For one-dimensional questions, filters, joins, and anything more surgical, run real queries against your file with the Query CSV with SQL guide. And when you do not yet know which fields are worth pivoting, profile the file first with the CSV Explorer, which shows per-column types, distinct values, and distributions at a glance.

Try it on your own file

The Pivot CSV tool runs entirely in your browser, so nothing you paste is uploaded anywhere. Paste a CSV, pick a rows field, optionally a columns field, a value field, and one of count, sum, avg, min, max, or first, and the summary updates as you type. Copy the result back out as CSV and it will paste cleanly into a spreadsheet or a report.

Sources

• Microsoft Support: Create a PivotTable to analyze worksheet data
• Hadley Wickham: Tidy Data (Journal of Statistical Software)