Cross tabulate two columns

One column goes down the side, another across the top, and each cell holds a number computed from the rows that carry both values. Totals run down the right and along the bottom, the cells are shaded by size so the pattern is visible before you read a single figure, and a column with too many distinct values is refused rather than attempted.

The guard is the feature

A crosstab turns every distinct value of one column into a column of the output. That is the whole idea, and it is also a loaded gun: point it at an id column on a file of fifty thousand rows and you have asked for a table fifty thousand columns wide.

This is not hypothetical. Testing a competitor's crosstab for this build, we loaded a 50,000-row file, set the row dimension to region and left the column dimension on its default, which was id. The browser tab died. Not an error message, not a spinner that never finished: the renderer ran out of memory, the page went to about:blank, and everything in the tab was gone. Reproduced twice. Their default state has all three dimensions set to the first column of the file, which on any real export is the id, so the page arrives already pointed at the failure.

Two things here are built around not doing that. The first is the default: the opening state picks the lowest-cardinality text column that is not named like an identifier, for both dimensions, and a numeric column that is a quantity rather than a key for the measure. On the sample that lands on region, product and amount, which is the crosstab a person would have built by hand.

The second is the refusal. Above 200 distinct values the tool stops and says so, by name:

"id" has 50,000 distinct values, so this crosstab would be 50,000
columns wide. Pick a column with fewer values, or use the group-by
tool, which puts those values down the side where a table can hold them.

The count is taken in one pass before a single cell is built, so the refusal is instant rather than arriving after thirty seconds of work. And it names the alternative, because "too many values" without a suggestion is a dead end and the suggestion is genuinely the right one: rows can go on forever, columns cannot.

Worked example: amount by region and product

Ten orders. Rows region, columns product, measure amount, aggregate sum:

region,Doohickey,Gadget,Thing,Widget,Total
East,76.25,,240.00,320.49,636.74
North,,,45.10,15.75,60.85
South,,64.00,,,64.00
West,310.20,220.40,,,530.60
Total,386.45,284.40,285.10,336.24,1292.19

Four things worth noticing. Empty cells are genuinely empty rather than zero, because "no Gadget orders in the East" and "$0 of Gadget orders in the East" are different statements and only one of them is true. Every figure has two decimal places, matching the source column. The row totals and the column totals both come from the raw values rather than from the cells beside them. And the grand total in the corner agrees with both, which it would not if it were adding up rounded numbers.

On screen the same table is shaded: the darker a cell, the larger the number. The Total row and Total column are deliberately left out of the scale. They are always the biggest numbers in the grid, and letting them set the top of the range washes every real cell out to nearly nothing, which is the commonest way a heat map ends up conveying no information at all.

Several dimensions down the side

The row side takes as many columns as you tick. Two gives you a nested label: every combination of the two that occurs in the data becomes a row, sorted so the first column's values group together.

The column side takes exactly one, and that asymmetry is on purpose rather than a limitation we did not get round to. Rows can go on for as long as your data does; a table three thousand rows long is fine to scroll and fine to open in a spreadsheet. Columns cannot: past a few dozen the table stops fitting on a screen, past a few hundred it stops being readable at all, and past sixteen thousand Excel will not open it. Crossing two column dimensions multiplies their cardinalities together, so two innocent-looking columns of thirty values each produce nine hundred columns. If you need that, put one of them on the row side.

Choosing the aggregate

Eight of them: sum, average, count, count distinct, minimum, maximum, median and the 90th percentile. Leave the measure column empty and every cell counts rows, which is the contingency table version and often the fastest way to see the shape of a dataset: how many records exist for each combination, and which combinations have none at all.

Count distinct is the one that surprises people with how useful it is here. Rows region, columns month, measure customer_id, count distinct, and you have active customers per region per month, which is a genuine business metric produced by three dropdowns.

Median and p90 in a crosstab give you something an average cannot. Response time by service and day, averaged, tells you almost nothing because a handful of slow requests move the mean for the whole cell. The same grid at p90 shows you exactly which service and which day the tail went bad.

What the output is for

Reading. A crosstab is the densest way to show two categories against a number. The shading means you see the pattern before you read any figures, and the pattern is usually the point.
Pasting into a report. The result is already the shape a table in a document wants. Copy puts it on the clipboard as a real table, so it lands in Docs or Word with its cells intact rather than as a block of comma-separated text.
Charting. A grid of this shape is exactly what a stacked bar chart or a heat map wants as its input. Send it to the dashboard builder and the chart types that suit two dimensions are the ones offered.
Spotting the holes. Empty cells are information. A product with no sales in one region, a month with no records for one team, a status that only one system ever produces: those show up as gaps in a grid and are invisible in a list.

Details

Blank values get a label. A row or column whose value is empty appears as (blank) rather than as an unnamed column, because an unlabeled column in a downloaded CSV is a puzzle for whoever opens it next.
Column order is sorted, and numerically when it can be. A column dimension holding 1 through 12 comes out in that order rather than 1, 10, 11, 12, 2, which is what sorting them as text would give you.
The same column cannot be both dimensions. Asking for that produces a diagonal and nothing else, so it is refused with a message rather than answered literally.
Non-numeric values are counted and reported. Text in the measure column is left out of the arithmetic rather than treated as zero, and the number left out is named above the result.
Shading is display only. The downloaded CSV holds the numbers, with no formatting attached. Turn the shading off if the tinted preview is harder for you to read than a plain one.

Frequently Asked Questions

Why does the tool refuse to use my column?

Because it has more than 200 distinct values, and each one would become a column of the output. A table with thousands of columns will not open in a spreadsheet, will not fit on a screen, and takes long enough to build that the tab stops responding. The message names the column and its count. Either pick a column with fewer values, or move that column to the row side, where there is no such limit.

What is the difference between this and the group-by tool?

The arithmetic is identical; the layout is not. Group by puts every combination on its own row, so region and product gives you sixteen rows and one number column. A crosstab puts one of those dimensions across the top instead, so you get four rows and four number columns. Group by is better for feeding another tool; a crosstab is better for reading, and much better for spotting the combinations with no data at all.

Why are some cells empty rather than zero?

Because no rows had that combination, which is not the same as rows summing to zero. If the East had one Gadget order for $0.00 the cell would read 0.00; if it had no Gadget orders at all the cell is blank. Collapsing those two into the same output loses real information, and it is the kind of loss that turns into a wrong conclusion in a meeting.

Can I have two columns across the top?

No, and deliberately. Crossing two column dimensions multiplies their cardinalities: two columns of thirty values each produce nine hundred output columns. Tick both on the row side instead, which gives you every combination as a nested row label and has no such ceiling.

How is the shading calculated?

Each numeric cell is tinted in proportion to where it falls between the smallest and largest value in the grid, with the Total row and Total column excluded from the scale. Totals are always the largest numbers present, and including them flattens every real cell to almost no tint at all. Shading is preview only and never appears in the downloaded file.

Do the totals add up the cells above them?

No, they are computed from the raw rows. For a sum the two agree. For an average, a median, a percentile or a count distinct they do not, and the raw-value version is the correct one: the average of four cell averages ignores that the cells hold different numbers of rows, and a customer appearing in two cells is one distinct customer in the total.

Build a crosstab that will not kill the tab

Sensible defaults, totals both ways, shading that means something, and a refusal when the answer would be unreadable.

Back to the crosstab tool