Home · Free tools · Glossary · Pivot table
What is a pivot table?
A pivot table turns a list of records into a summary grid. You choose one field to run down the side, another to run across the top, and a number to aggregate in the middle. Five thousand transactions become twelve months by six categories, with totals — without writing a formula.
The one rule that makes pivots behave
The source has to be one row per event, one column per attribute, and no blank spacer rows. Most pivot frustration is a source-data problem wearing a pivot-table costume:
- A table that already has subtotal rows in it will double-count them.
- Months spread across columns cannot be pivoted by month — the data has to be long, not wide, before it can be made wide again in a different direction.
- Merged cells break the column structure a pivot depends on.
- A blank row is read as the end of the table.
The three arguments people actually get wrong
- Count versus count of distinct. "How many customers" is almost never a plain count of rows.
- Sum versus average. Averaging an already-averaged column produces a number that means nothing.
- Blank versus zero. A blank cell is excluded from an average; a zero is included. They give different answers and both look reasonable.
Pivot table versus GROUP BY versus formula column
They are the same operation in three costumes. SQL's GROUP BY is a pivot without the
cross-tab. A formula column that aggregates by key does the same thing but leaves the answer attached
to each row, which is what you want when the summary has to join back to the detail.
Pivot when you want a report to look at. Formula column when the answer has to feed something else.
Try it without installing anything
The monthly report generator on this site is a pivot with the arguments pre-decided: date down one axis, a category across the other, an amount in the middle. It runs in your browser and nothing is uploaded.
Questions people ask
Why does my pivot table show wrong totals?
Usually the source data: subtotal rows already inside the range, merged cells, a blank row cutting the table short, or blanks being treated differently from zeros in an average.
What is the difference between a pivot table and GROUP BY?
The same aggregation. GROUP BY returns a long result; a pivot cross-tabulates it into a grid with one field across the top. A formula column is a third form, where the aggregate stays attached to each row.
Can I pivot without Excel?
Yes — every spreadsheet has pivots, and the monthly report generator here does the common month-by-category case in the browser with nothing uploaded.
Related
Doing this every month?
In TableDI 2 you do it once, then save it as a job. Next month you drop in the new files and run it again.
macOS, Apple silicon and Intel; Windows is in progress (what to do meanwhile). Free is not a trial — no account, no card.
Last reviewed 2026-09-11 by the TableDI team. Something wrong on this page? Tell us — it is one inbox, read by the people who build TableDI.