Pivot Tables, Explained

After reading this you will know exactly how a pivot table turns a flat CSV into a cross-tabulated summary, how each aggregation is computed cell by cell, and how to read the totals without fooling yourself.

A pivot table takes many rows and collapses them into a compact grid. You pick a field for the rows, a field for the columns, one field to aggregate, and a rule for combining values (count, sum, average, min or max). The tool then walks every row of your CSV, drops it into the right cell of the grid, and applies the rule.

Here is the hook. Suppose you have 500 sales records with a region column and an amount column. You want to know total sales per region. That is a pivot: rows = region, values = amount, aggregation = sum. What was 500 lines of raw data becomes a four-line answer.

What a pivot table actually does

Think of your CSV as a stack of index cards, one per row. A pivot table sorts those cards into bins. The row field decides which shelf a card goes on. The column field decides which slot on that shelf. Once every card is filed, the tool reduces each slot to a single number using the aggregation you chose.

Two fields define the grid. If you leave the column field empty, you get a single stack of shelves: one row per group, one number per row. That is a grouped summary. Add a column field and the grid becomes two-dimensional, with a value in every intersection.

Row field

The category that labels each row of the output. Distinct values become distinct rows.

Column field

The category that labels each column. Optional. Distinct values become distinct columns.

Value field

The column whose numbers get aggregated. For count, any column works; the values are just tallied.

Aggregation

The rule that reduces the group of matching rows to one number: count, sum, average, min or max.

When to use it, and when not

Reach for a pivot when you have one row per event or record and you want a summary by category. Sales by region and quarter, error counts by service and day, average response time by endpoint: all pivots.

Do not use a pivot when you actually want to keep every row and only reshape or filter it. If you need to sort, hide columns or pull out a subset of rows, that is a job for the CSV Viewer & Editor. A pivot is lossy on purpose: after aggregation the individual rows are gone.

The five aggregations and their math

For a cell, let the matching values be x_1, x_2, \dots, x_n. Each aggregation reduces that set to one number.

Here n is how many rows fell into the cell, and x_i is the value from row i. Min and max are just the smallest and largest of the x_i.

Count is special. It tallies rows and never looks at whether the value is a number, so it works on any column. Sum and average require numeric values. Cells that do not parse as numbers are skipped, which means n for a sum can be smaller than the count of rows in the same cell. That gap is a frequent source of confusion, so hold onto it.

A worked example with the demo data

Load the demo. The sample is a small sales table:

Check the arithmetic two ways: the row totals add to 450 + 500 = 950, and the column totals add to 370 + 580 = 950. If those two do not match, something dropped a value. They should always agree for count and sum.

See how the aggregation changes the answer

The same grid tells a different story under each rule. Take the North+Widget cell, which holds the values 100 and 150. Count gives 2, sum gives 250, average gives 125, min gives 100, max gives 150. The bins never move; only the reduction changes.

Reading and interpreting the result

Read a pivot cell as a sentence. The cell at row North, column Widget, sum of amount, reads: "The total amount for North Widget sales is 250." Always name the aggregation out loud, because 250 means nothing until you know it is a sum and not a count or an average.

Totals deserve care. Row and column totals are trustworthy for count and sum: they are the sum of the cells. For average they are not. The average of the North row is not (125 + 200) / 2 = 162.5; the true average across all three North rows (100, 150, 200) is 450 / 3 = 150. An average of averages ignores how many rows sit behind each cell. When you need a grand average, aggregate the raw rows, do not average the cell averages.

Common mistakes

The mistakes below are the ones that quietly change your numbers.

• Non-numeric values in a sum. A cell holding 1,200 with a thousands separator, or $300, or N/A, does not parse as a number and is skipped. Your sum comes out low and you never see the dropped rows. Clean the column first.
• Trailing spaces splitting a group. "North" and "North " are two different row labels. You get two North rows that should have been one. Trim whitespace before pivoting.
• Case sensitivity. Widget and widget land in separate columns. Normalize case if they mean the same thing.
• Averaging averages. Covered above. It is the most common way to get a plausible but wrong headline number.
• Too many columns. A high-cardinality column field produces a table nobody can read. Move that field to rows, or leave the column field empty.

Related tools

Prepare and inspect your data before and after pivoting. Use the CSV Viewer & Editor to trim whitespace and drop bad rows first. If your data starts in a spreadsheet, run it through the Excel → CSV Converter to get clean CSV per sheet. Need the summary as structured data? Send the CSV output through the CSV ↔ JSON Converter. To paste the result into docs, the Markdown Table Generator turns rows into an aligned table. If the source contains personal data, mask it with the Data Anonymizer before you share the pivot. And if your data lives in columnar files, the Parquet & Feather Preview lets you peek inside first.

Related tools

Auto Analyst: Instant Statistical Report

Drop a CSV or Excel file and get a statistician’s report: data problems, distributions, every real relationship, key drivers and Simpson’s paradox warnings, in plain English.

CSV Viewer & Editor

View, edit, sort, filter and extract columns from CSV files.

CSV ↔ JSON Converter

Convert CSV to JSON and JSON back to CSV.

Data Visualizer

29 chart types — heatmap, treemap, dumbbell, slope, candlestick — with rich styling and annotations.

Excel → CSV Converter

Convert XLSX/XLS sheets to clean CSV, per sheet.

JSON Table Viewer

Paste JSON, get a sortable, searchable table.