Excel Transpose: How to Quickly Flip Data

How-to basics · Updated November 22, 2026

To quickly transpose data in Excel, select the range you want to flip, press Ctrl+C to copy, right-click the destination cell, and choose Transpose under Paste Options. This rotates your data so rows become columns and columns become rows, and it works in Excel for Microsoft 365, Excel 2021, and older versions alike.

What Transpose Actually Does

Transposing is a data rearrangement operation that swaps the orientation of a range. If you have a list of names in a column (A1:A5), transposing turns them into a row (A1:E1). If you have a table with headers in row 1 and data in rows 2–10, transposing makes those headers appear in column A and the data flow across columns. This is useful when you receive data formatted for one type of analysis but need it in another—for example, converting a vertical list of monthly sales into a horizontal timeline for a chart.

Excel offers three main ways to transpose: the paste-special method (quickest for one-time use), the TRANSPOSE function (dynamic, updates automatically), and Power Query (best for repeated or large data transformations). The method you choose depends on whether you need the result to stay linked to the original data.

Method 1: Paste Special (Static Result)

The paste-special transpose is the fastest and most intuitive. Here are the exact steps for Excel on Windows:

  1. Select the range you want to transpose (e.g., A1:C3).
  2. Press Ctrl+C to copy. On Mac, press Cmd+C.
  3. Click the cell where you want the transposed data to start (make sure there’s enough space—the result will occupy the opposite shape, so a 3-column by 2-row range becomes a 2-column by 3-row range).
  4. Right-click the destination cell, then under Paste Options, click the Transpose icon (it looks like a small table with arrows). Alternatively, use Home > Paste > Transpose on the ribbon.

The pasted data is static—it does not update if you change the original cells. This is fine for one-off conversions, but if you expect the source data to change, use the function method below.

Method 2: The TRANSPOSE Function (Dynamic)

If you want the transposed data to automatically reflect edits in the original range, use the TRANSPOSE function. This is an array function, so you must enter it with Ctrl+Shift+Enter in older Excel versions. In Excel 365, it works as a dynamic array that spills automatically.

Assume your data is in A1:C3 (3 columns, 2 rows). To transpose it into, say, E1:G2:

  1. Select the destination range E1:G2 (note: it must be the transposed shape—2 columns by 3 rows here).
  2. Type =TRANSPOSE(A1:C3).
  3. Press Ctrl+Shift+Enter (or just Enter in Excel 365, which handles dynamic arrays).

The formula returns the transposed data. If you change any value in A1:C3, the result updates instantly. One caveat: you cannot delete a single cell in the spill range—you must clear the entire range first. Also, the destination range must be empty before entering the formula.

Method 3: Power Query (For Repeated or Large Data)

Power Query is a data transformation tool built into Excel (Data > Get & Transform Data). It’s overkill for a small range, but ideal if you transpose data from a CSV or database repeatedly. To use it:

  1. Select your data range and go to Data > From Table/Range (Excel will convert it to a table if not already one).
  2. In the Power Query editor, go to Transform > Transpose.
  3. Click Close & Load to put the transposed data back into a worksheet.

Power Query’s transpose is static once loaded, but you can refresh the query (Data > Refresh All) if the source changes. It handles large datasets more efficiently than copy-paste, and it preserves formatting better in some cases.

How to Choose the Right Method

Here’s a quick decision guide:

  • One-time quick flip: Paste Special Transpose.
  • Data will change and you want auto-updates: TRANSPOSE function.
  • Large dataset or recurring import from external files: Power Query.
  • You need to transpose only values (no formulas): Paste Special with Values and Transpose both selected.

For most users, paste special is the answer. The function is for those who understand array formulas. Power Query is for analysts who work with big data.

Common Problems and Solutions

Transposing can hit a few snags. Here are the typical issues and fixes:

  • “Cannot change part of an array” error: This happens when you try to edit a single cell that is part of a TRANSPOSE array. Select the entire spill range, press Delete, then re-enter the formula.
  • Destination overlaps source: If you paste transpose into a range that overlaps the original data, Excel will complain. Choose a destination that doesn’t overlap, or copy to a new sheet.
  • Formulas get transposed incorrectly: Paste Special Transpose also transposes cell references. If your original cells contain formulas like =A1+B1, the transposed version will adjust references, which may not be what you want. To keep only values, use Paste Special > Values first, then transpose.
  • Blank cells become zeros: When you transpose a range with blanks, Excel may convert them to 0 if you use the TRANSPOSE function. To avoid this, use Paste Special and choose Skip Blanks (available in the Paste Special dialog).

Transpose vs. Other Data Reshaping Tools

Transpose is just one way to reshape data. Excel also has Unpivot (via Power Query) to turn wide tables into long ones, and Flash Fill (Ctrl+E) to manually rearrange text patterns. If you need to flip data upside down (reverse order), transpose won’t help—you’d use a helper column with INDEX or the SORTBY function. For example, to reverse a vertical list in A1:A5, you could use =SORTBY(A1:A5,ROW(A1:A5),-1) in Excel 365.

Transpose is also different from rotating a chart—when you switch rows and columns in a chart, you’re not changing the underlying data, just the chart’s orientation. That’s done via Chart Design > Switch Row/Column.

Frequently Asked Questions

Does transposing work with merged cells?

No. Transpose does not work with merged cells. If your range contains merged cells, Excel will either paste them as regular (unmerged) cells or give an error. Unmerge the cells first, then transpose, and re-merge if needed.

Can I transpose data without using the mouse?

Yes. Use the keyboard: select the range, press Ctrl+C, move to the destination cell, then press Ctrl+Alt+V to open Paste Special. On Mac, use Cmd+Ctrl+V. In the Paste Special dialog, press E to select Transpose, then press Enter.

Why does my TRANSPOSE formula show a #SPILL! error?

This happens in Excel 365 when the destination range isn’t empty or there’s something blocking the spill area. Clear the cells where the transposed data should appear, and make sure there are no merged cells or other data in that block.

How do I transpose a table but keep the column headers?

If you transpose a table that includes headers, the headers become the first row of the transposed data. To keep headers as headers, you’ll need to manually re-enter them after transposing, or use Power Query and set the first row as headers after transposing (Home > Use First Row as Headers).

Is there a way to transpose data automatically when I paste?

No, Excel doesn’t have a setting to auto-transpose on every paste. You can create a macro to do it, but that’s beyond normal usage. The TRANSPOSE function is the closest to automatic, as it updates when the source changes.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.