🔁 Data Reshaping · 7 min read

How to Transpose a CSV File: Excel, Sheets, Python (2026)

Transposing a CSV means swapping its rows and columns, so the horizontal header becomes the left-hand column and each record becomes a column of its own. In Excel, copy the range and use Paste Special > Transpose. In Google Sheets, use Edit > Paste special > Paste transposed. In Python, zip(*rows) flips a list of rows, and DataFrame.T flips a pandas table in one attribute. Whichever you pick, let a real parser read the file: a CSV field may legally contain a comma or a line break inside quotes, and a hand-written split on commas will mangle those rows.

What Transposing Actually Changes

Nothing about the values. A transpose is a reshape, not an edit, so every cell keeps its contents and only its position changes. A table with three columns and forty rows becomes a table with forty columns and three rows.

People reach for it for a few predictable reasons. A survey export arrives as one column per question and you want one row per question. A vendor sends a wide price list and your import expects the fields stacked in a single column. A chart tool wants categories down the left, not across the top. In each case the data is fine and the orientation is wrong.

What a transpose does change, and this catches people, is the header. There is no concept of a header row in the CSV format. The first line is just the first record. Once you flip the file, that first record is now sitting in the first column, and it looks like data rather than labels.

Why You Cannot Just Split on Commas and Swap

RFC 4180 is the document that defines the comma-separated values format. It says a field may be wrapped in double quotes, and inside those quotes a field is allowed to hold commas, line breaks, and doubled quote marks used to represent a literal quote.

That single rule is why naive transposing fails. A row like Smith, "Loves cats, dogs, and birds", 42 has three fields, not five. Split it on commas and you invent two fields that never existed, then flip the table and the extra fields propagate down a column that should never have had them.

The corruption is quiet. You get a table of the right size with plausible values in the wrong places, and nothing throws an error. Every method below avoids it by using a parser that already knows the rule.

Method 1: Excel, With Paste Special

This is the fastest route for a table that fits on one screen or two.

1. Open the CSV. If it has an ID or ZIP column, open it through Data > From Text/CSV so the leading zeros survive.
2. Select the range you want to flip and copy it.
3. Click the top-left cell of an empty area, or a fresh sheet.
4. Right-click, choose Paste Special, then Transpose.

The ceiling is the worksheet itself, which Microsoft documents as 1,048,576 rows by 16,384 columns. Transposing turns rows into columns, so a source table with more than 16,384 rows would need more columns than a sheet has, and Excel will refuse. For anything that wide, use Python.

One more limit worth knowing: a single Excel cell holds 32,767 characters. If you transpose a narrow file into a very long row, a cell that merges several fields can hit that wall without warning.

Method 2: Google Sheets

Sheets does the same job with its own menu, and it is the easier of the two to share.

1. Select the range and copy it.
2. Use Edit > Paste special > Paste transposed, or the shortcut Ctrl+Shift+V on Windows and Cmd+Shift+V on a Mac.
3. Paste into a sheet with room to grow to the right.

Google caps a spreadsheet at 10 million cells. A transpose keeps the cell count identical, so the limit is not the total so much as the shape: a 20,000-row table becomes a 20,000-column one, and Sheets allows 18,278 columns, so it can take a bit more than Excel before it gives up.

Method 3: Python, the Version That Scales

For anything you will run more than once, do it in code. The standard library is enough for most files.

import csv

with open("survey.csv", newline="", encoding="utf-8") as f:
    rows = list(csv.reader(f))

# zip(*rows) groups the first element of every row, then the second, and so on
flipped = list(zip(*rows))

with open("survey-transposed.csv", "w", newline="", encoding="utf-8") as f:
    csv.writer(f).writerows(flipped)

The newline="" argument is not optional decoration. The csv module documents it as required: without it, Python's own newline translation can insert blank lines between records on Windows, and quoted fields that contain a line break can be written back out in a way your next reader mis-parses.

If the file is already a DataFrame, pandas has the whole operation as one attribute:

import pandas as pd

df = pd.read_csv("wide-prices.csv", dtype=str)
df.T.to_csv("long-prices.csv", header=False)

Reading with dtype=str keeps identifiers as text, which matters after a transpose because a column of numbers that used to be a row of codes is exactly where a leading zero disappears.

Method 4: Command Line, for Big Files

If the file is too large for an editor and you want no code, csvtk does it in one command:

csvtk transpose survey.csv > survey-transposed.csv

It streams the file and respects quoting, so it handles the quoted-comma cases that a text split breaks. It is the right tool when the file lives on a server and you would rather not move it.

Which Method Fits Your File

Getting the Header Back Where You Want It

A transpose moves the header row into the first column, which is rarely what you meant. The fix is to read the header, hold it to one side, flip the rest, and re-attach the labels in the shape you actually need. In pandas that is two lines: take df.columns as a list, transpose, then assign the list as the new index.

If the source file is also messy, transpose it after the cleanup, not before. Fixing an encoding problem or removing blank rows in a flipped file is harder, because every tool that helps you with those tasks assumes fields run down the page. The delimiter guide covers the semicolon and tab cases, and the sorting guide handles ordering once the shape is right. When you want to see what the file really contains before you reshape it, the free CSV analyzer lists the columns and types without touching a value.

Frequently Asked Questions

What does it mean to transpose a CSV file?

Transposing swaps rows and columns. The header row that ran across the top becomes the first column down the left, and each data row becomes a column. The values do not change, only their arrangement.

Can I transpose a CSV by splitting it on commas?

No. RFC 4180 allows a field to be quoted, and a quoted field may hold commas, line breaks and doubled quotes. A text split on commas invents fields that never existed. Use a parser, which every spreadsheet and the Python csv module already are.

How do I transpose a CSV in Excel?

Copy the range, right-click the top-left cell of an empty area, choose Paste Special, then Transpose. A worksheet holds 16,384 columns, so a table with more than 16,384 rows cannot be flipped this way in one sheet.

How do I transpose in Google Sheets?

Copy the range, then Edit > Paste special > Paste transposed, or Ctrl+Shift+V. Sheets keeps quoted commas intact because it parsed the file properly on import.

How do I transpose a very large CSV?

Use Python or csvtk. A spreadsheet transpose is bounded by the grid, so a 50,000-row table would need 50,000 columns, which no spreadsheet has. Python's zip and pandas' T attribute do not care about the shape.

Does transposing keep my header row?

The header becomes the first column, which is usually not what you want. A CSV has no header flag, so the tool cannot tell labels from data. Hold the first row aside, flip the rest, and re-attach the labels.

Will transposing change my data types?

A CSV has no types, so there is nothing to preserve. Problems start when the receiving tool guesses: a transposed column of mixed values can look numeric and drop a leading zero. If a value is an identifier, keep it as text.