How to Transpose in Excel: Rows to Columns

To transpose in Excel is to flip a table on its side — rows become columns, and columns become rows. Unlike retyping the data, every method below moves the values in one action. Here is how to transpose in Excel with Paste Special, the TRANSPOSE function, and Power Query, plus the Google Sheets equivalent and the CSV case.

The job usually arrives the same way. Some system exported a table with the field names running down column A and one record per column across the sheet, and the thing you need to do next — a chart, a pivot table, an import — wants field names across the top and one record per row.

Paste Special is the fastest transpose

For a one-off flip, Paste Special is the shortest path and needs no formulas.

Select the range you want to rotate and press Ctrl+C. Copy is mandatory here: Microsoft is explicit that using Cut or Ctrl+X will not work with Transpose [1]. Click the top-left cell of an empty area of the sheet, right-click, and pick the Transpose option from the paste icons. On Excel for the web the same command lives on the Home tab, under the Paste icon as Transpose Rows and Columns [1].

Two things go wrong often enough to plan around.

The first is space. The pasted table overwrites whatever occupies the destination cells — Microsoft's own wording is that it "will entirely overwrite any data / formatting that's already there" [1]. A 3-row by 40-column source needs 40 rows by 3 columns of genuinely empty room. Paste onto a new sheet if you are not certain.

The second is the greyed-out Transpose option, which is the most common reason people search for this in the first place. If your range is formatted as an Excel table, the feature is unavailable [1]. Click any cell in it, go to Table Design > Convert to Range, confirm, and the paste option comes back. You can also skip the conversion entirely and use the function below.

Paste Special produces a static copy. Change a value in the original afterwards and the flipped version keeps the old number — which is exactly what you want when the point of the exercise was to restructure the file once and move on.

The TRANSPOSE function keeps the copy live

Use the function when both orientations need to stay in sync, because it returns a reference rather than a snapshot.

The syntax is TRANSPOSE(array), where the array is the range to rotate [2]. How you enter it depends on your Excel version, and this is where most of the confusion around the function comes from.

In Microsoft 365 and Excel 2021 or later, type the formula into the top-left cell of the output area and press Enter. Dynamic arrays handle the rest and the result spills across the cells it needs [2]. In older versions the same formula is an array formula: select an output range whose dimensions are the reverse of the source — a 3-row by 4-column source needs a 4-row by 3-column selection — and confirm with Ctrl+Shift+Enter, which wraps the formula in curly brackets for you [2].

The output range has to match. Microsoft's requirement is that it hold the same number of rows and columns, respectively, as the source has columns and rows [2]. Get that backwards in a legacy version and you get #VALUE! errors in the cells that had nowhere to draw from.

A live transpose is worth the extra step when a summary table feeds a dashboard and the source table is still being edited. If nothing downstream depends on the flipped copy, Paste Special is less to maintain.

Power Query transposes on every refresh

When the same badly-shaped export lands every week, do the flip once in Power Query and let it reapply itself.

Load the range or file with Data > Get Data, then in the Power Query Editor use Transform > Transpose. Under the hood this is Table.Transpose, which the M reference describes as making columns into rows and rows into columns [3].

There is one step people miss. Transposing treats your headers and your data differently — the headers are not part of the table body, so a straight transpose leaves the old field names stranded. The fix is to demote the headers into a first row before transposing, then promote the new first row into headers afterwards. Both commands sit on the Home tab: Use First Row As Headers promotes, and the arrow next to it offers Use Headers as First Row to demote [4].

The payoff is that next month's file is a right-click and Refresh. That matters more than the initial time saved, because a query cannot forget a step at 6pm on a deadline the way a manual paste can.

Transpose in Google Sheets

Google Sheets offers both routes with the same trade-off between them.

For a static copy, select and copy the range, right-click the destination, and choose Paste special > Transposed. For a live one, use the function: TRANSPOSE(array_or_range) transposes the rows and columns of a range, so that the value in the nth row and mth column becomes the value in the mth row and nth column [5]. Sheets treats it as an array function, so =TRANSPOSE(A2:F9) in one cell fills the whole rotated block.

Because Sheets runs in a browser tab, this is also the quickest way to flip a file on a machine where you cannot install Excel — worth knowing if you are working from a locked-down laptop.

Transposing a CSV export before an import

If the file you are fighting is a CSV rather than a workbook, transposing is one step in a longer cleanup, and it is worth knowing which parts need a spreadsheet at all.

A CSV file is plain text: one line per row, values separated by commas. Nothing in it is locked to a program, so opening it in Excel, Google Sheets, or the browser version of either gets you to Paste Special. Do the transpose wherever it is convenient. What usually follows is the part that actually decides whether the import succeeds — duplicate records, phone numbers in five different formats, a Full Name column the destination system wants split in two, and trailing spaces that make two identical values look different.

That cleanup does not need a spreadsheet, and there is a reason to keep it out of one when the file holds customer or employee data. Sigmera's in-browser cleaning tools read the file into the page with client-side JavaScript and hand back a download — the file is never uploaded to a server, so there is no third party processing your contact list and no GDPR question about where it went. You can strip duplicate rows in one pass, merge two columns into one when the destination expects a single field, and check the result before exporting; the cleaning walkthrough covers the order that causes the least rework.

One practical note on direction. A transposed export often needs to end up as a workbook rather than a CSV — a flipped table with typed columns survives better as .xlsx than as text, since a CSV has no data types to preserve. The CSV to Excel converter does that conversion in the browser too.

Which method to reach for comes down to how many times you will do it. Once: Paste Special. Whenever the source changes: the TRANSPOSE function. Every week on a new export: Power Query. The mistake worth avoiding is the fourth option — retyping the table by hand, which is where the transcription errors come from that no amount of cleaning downstream can catch.