Turn a table on its side

Rows become columns and columns become rows. The first column's values become the new header, so a table with one row per region comes back with one column per region. Transpose it twice and you get the file you started with, byte for byte, because every cell is moved as text and nothing is reinterpreted along the way.

When a table is the wrong way round

Transposing is a small operation with a handful of very specific reasons behind it, and if you are here you probably have one of them:

A report built for humans, needed by a machine. Finance and survey exports often put metrics down the side and periods across the top, because that reads well on a page. Every tool that wants to chart it, load it or query it wants one row per observation instead.
A single record with far too many fields. One row and eighty columns is unreadable. Transposed it is eighty rows of field and value, which you can actually scan, and this is by some distance the fastest way to inspect one record from a wide export.
A tool that wants the other orientation. Some chart libraries and some import formats are opinionated about which axis is which, and arguing with them costs more than flipping the file.
Comparing two things side by side. Two rows of the same shape are hard to compare because your eye has to travel sideways twice. Two columns are easy, because the fields line up vertically.

What transposing is not is a way to turn a wide table into a long one. Those get confused constantly. Transposing swaps the axes and keeps the same number of cells; unpivoting turns a grid of numbers into one row per value with an id beside it. If you have twelve month columns and you want a month column, you want the wide-to-long tool and not this one.

Worked example, and the round trip

Here is a small wide table, sales-wide.csv:

region,product,jan,feb,mar,apr
East,Widget,1200.50,1310.00,1288.25,1402.75
East,Gadget,980.00,1015.40,,1120.00
West,Widget,1105.25,1180.75,1240.50,1198.00
...

Transposed, with the first column supplying the new header. Because region repeats, the duplicates are numbered:

region,East,East_2,West,West_2,North,North_2,South,South_2
product,Widget,Gadget,Widget,Gadget,Widget,Gadget,Widget,Gadget
jan,1200.50,980.00,1105.25,875.60,640.00,512.75,430.20,388.90
feb,1310.00,1015.40,1180.75,910.20,702.35,,455.00,401.25
mar,1288.25,,1240.50,955.00,688.10,540.25,470.65,415.00
apr,1402.75,1120.00,1198.00,1002.40,715.90,566.80,489.10,428.55

Eight rows by six columns became six rows by nine columns. The old header is now the first column of values, the old first column is now the header, and the blank cell in mar is still blank rather than having become a zero.

Run that output back through the page and you get the input again exactly, which is the property worth checking on any transpose tool. It holds here because nothing is ever parsed: 1200.50 is moved as the six characters it is, not as a number that would print back as 1200.5.

Duplicate labels, and why they get numbered

When the first column supplies the new header, its values have to be unique, because column names have to be unique. Real files do not cooperate: a table with one row per region and product will have the region repeated four times down the first column.

Repeats get a numeric suffix, and the count of them is reported above the result. East appearing three times becomes East, East_2 and East_3. Nothing is dropped and nothing is merged, which are the two things a tool must not do quietly here: merging would silently lose two thirds of the data, and dropping would lose it outright.

If the numbered names are not useful to you, the fix is upstream. Merge the label columns into one first with a merge-columns step, so East and Widget become East Widget, and then transpose.

The width guard

A transposed file has exactly one column for every row of the input. A thousand rows in means a thousand columns out. Ten thousand rows means a file that Excel will open, because its limit is 16,384 columns, but that no human will ever read and that most tools will handle badly.

Above 2,000 columns the tool stops and says what the number would have been, and suggests filtering or sampling the rows first. That is a lower ceiling than the spreadsheet's, deliberately: by the time you are past two thousand columns, transposing is almost certainly not the operation you wanted, and a refusal that says so is more useful than a file that technically exists.

If you genuinely need to flip something enormous, cut it down first with a sample or a filter.

What survives the trip

Leading zeros. A postal code of 01234 comes out as 01234. Every cell is moved as text and never parsed as a number, which is the single commonest way a spreadsheet ruins an id column.
Trailing decimals. 120.50 stays 120.50 rather than becoming 120.5.
Long numeric ids. A sixteen-digit account number does not turn into scientific notation, because nothing here ever converts it to a float.
Empty cells. Blanks stay blank rather than becoming zero. A ragged input is padded to a rectangle first, and the number of rows that needed padding is reported.
Quoted fields. A cell holding a comma, a newline or a quote stays one cell and is re-quoted properly in the output.

Frequently Asked Questions

Is transposing the same as pivoting?

No, and this is the confusion worth clearing up. Transposing swaps the two axes and keeps exactly the same cells: an 8 by 5 table becomes a 5 by 8 table. Pivoting aggregates, so several input rows collapse into one output cell. If you want one dimension down the side, another across the top, and a computed number in each cell, you want the crosstab tool. If you just want the table the other way round, you want this one.

Can I transpose it back?

Yes, exactly. Run the output through this page again with the same setting and you get the original file, cell for cell, as long as the first column's values were unique. If they were not, the round trip brings back the numbered names rather than the repeated originals, because the numbering is what made the intermediate file valid.

What happens to my column names?

They become the values of the first column of the output, so nothing is lost. The header of that new column is taken from the header of the input's first column, which is what makes the round trip work. In generic mode you name it yourself and it defaults to field.

Two rows have the same value in the first column. What happens?

They get numbered: East, East_2, East_3. Column names have to be unique, so the alternatives would be dropping rows or merging them, both of which lose data silently. The count of renamed columns is reported above the result so you know it happened.

Why is there a limit of 2,000 columns?

Because a transposed file has one column per input row, and past a couple of thousand the result is not usable by anyone. Excel technically allows 16,384 but will not display them usefully, and most other tools degrade well before that. The message names the number your file would have produced. Filter or sample the rows down first.

Will my leading zeros survive?

Yes. Every cell is carried across as the exact string that was in the file, and nothing is ever parsed as a number and re-printed. That is the whole reason to use a dedicated tool for this rather than opening the file in a spreadsheet and using paste-special, which will happily turn 01234 into 1234 on the way through.

Flip your table

Rows to columns, columns to rows, and back again with nothing lost on the way.

Back to the transpose tool