Turn rows into columns

A table with a month column and a value column becomes a table with one column per month. Say which columns identify a row, which column supplies the new column names, and which holds the values. When two rows land in the same cell you choose what happens, and you are told how often it happened rather than losing one of them quietly.

The three roles, and getting them the right way round

Every long-to-wide reshape is the same three-part question, and every mistake is one of the three columns being in the wrong slot:

Keep down the side are the row keys. Each distinct combination of these becomes one row of the output. In a sales file that is region and product; in a survey it is the respondent id.
New column names from is the column whose values become the header. Month, quarter, question id, metric name. This is the one that has a limit on it, because every distinct value becomes a column.
Values from is the column that fills the cells. Amount, score, count, duration.

The tool guesses all three on load and gets it right on a normally-shaped long file, but the guess is a guess. The tell that it is wrong is a result with hundreds of columns and one row, or four columns and one row per input row: the first means the name and key columns are swapped, the second means the name column is something nearly unique.

Worked example

Eighteen rows of long-format sales:

region,product,month,amount
East,Widget,jan,1200.50
East,Widget,feb,1310.00
East,Widget,mar,1288.25
East,Gadget,jan,980.00
...

Keys region and product, names from month, values from amount:

region,product,feb,jan,mar
East,Widget,1310.00,1200.50,1288.25
East,Gadget,1015.40,980.00,1120.00
West,Widget,1180.75,1105.25,1240.50
West,Gadget,910.20,875.60,955.00
North,Widget,702.35,640.00,688.10
North,Gadget,540.25,512.75,566.80

Eighteen rows became six. The month columns are in alphabetical order, because jan and feb are just strings and nothing tells the tool they are months. Name them 2024-01 and they sort correctly, which is a good argument for that naming everywhere. The row order follows the order the key combinations first appeared in the input.

When two rows land in the same cell

This is the real difference between a reshape and a pivot, and it is where a tool either tells you the truth or quietly loses data.

If your keys plus the name column uniquely identify every input row, nothing collides and the reshape is lossless. If they do not, several rows want the same cell, and something has to happen. Most tools take the first one and say nothing, which means the file you download is missing rows and looks completely normal.

Here you pick the aggregate, from the same eight the summarising tools use, and you are told how many rows collided:

4 rows landed in a cell that already had a value. They were combined
with sum; add another identifier column if each combination should
be unique.

That message is the point. Sometimes summing is exactly right, because the input genuinely has several transactions per region per month and you want the monthly total. Sometimes it means you forgot a key column and the output is quietly wrong. The number tells you which situation you are in, and the suggestion tells you the fix.

One special case: a cell that receives exactly one non-numeric value keeps it as it is. Summing a category label would print an empty cell where the data plainly said yes, so text passes through untouched when there is nothing to combine it with.

Gaps, and what to write in them

Not every combination occurs. A product that launched in March has no January column value, and a wide table has to put something in that cell.

The default is blank, which is the honest answer: there was no observation. Set Write this where there is nothing to 0 when the absence genuinely means zero, which is common for counts and sales and wrong for measurements like temperature or score. It is a real distinction and the tool will not guess at it for you, because guessing wrong turns "we have no data" into "the value was zero", and those lead to different decisions.

You can also write n/a or a dash, which is useful when the wide table is going into a document for people to read rather than into another tool.

The cardinality limit

Every distinct value in the name column becomes a column of the output, so the same guard applies here as on the crosstab: above 200 distinct values the tool refuses and names the column and its count.

The usual cause is pointing the name column at something that is nearly unique, like a transaction id or a timestamp with seconds in it. Both produce a table with one column per input row, which is not a reshape, it is a very slow transpose. The fix is either a genuinely categorical column, or bucketing the one you have first: truncate the timestamp to a month with a calculated column and reshape on that.

Frequently Asked Questions

How is this different from a pivot table?

A pivot table always aggregates, and a value column is required. This reshapes first and only aggregates when it has to, which is when two input rows want the same cell. If your long data has exactly one row per key-and-name combination, this is lossless and reversible; a pivot table would apply an aggregate to groups of one and get the same numbers, but the framing is different and so is what happens when the data is not that tidy.

Why are my month columns in the wrong order?

Because jan, feb and mar are strings, and sorted as strings they come out apr, feb, jan, mar. Nothing in the file says they are months. Rename them to 2024-01, 2024-02 and 2024-03 before reshaping and they sort correctly, and they will keep sorting correctly in every other tool you take the file to.

What happens when two rows want the same cell?

They are combined with the aggregate you chose, and the number of collisions is reported above the result. Sum is the default. If each combination was supposed to be unique, that message means you are missing a key column, and adding it will make the collisions disappear. This is deliberately loud, because the alternative is a file that silently dropped rows.

Should empty cells be zero or blank?

It depends on what the absence means. For counts and sales, a missing combination usually means zero happened, and writing 0 makes the table easier to sum. For measurements like temperature, price or score, there is a real difference between zero and no reading, and writing 0 would fabricate data. Blank is the default because it never fabricates anything.

Can I undo this?

Yes. Send the wide result to the wide-to-long tool with the same identifier columns and you get the long format back, as long as no cells were combined on the way out. The two tools are exact inverses of each other when the reshape was lossless, which the collision count tells you.

Why is there a 200-column limit?

Because each distinct value in the name column becomes an output column, and a table with thousands of columns is not usable by any tool or any person. The refusal names the column and how many values it holds, which is usually enough to see that the wrong column is in that slot. Bucket the values first if you genuinely need many of them.

Spread the values across

One column per value, with the collisions counted rather than swallowed.

Back to the long-to-wide tool