Excel Function to Transpose Data (Step-by-Step)

Formulas & functions · Updated November 22, 2026

The fastest way to transpose data in Excel is to copy the range, right-click the destination cell, and choose Paste Special > Transpose. For a dynamic version that updates automatically, use the =TRANSPOSE() function with the original range as its argument, entered as an array formula (Ctrl+Shift+Enter in older Excel, or just Enter in Excel 365).

What Does Transposing Mean in Excel?

Transposing flips the orientation of a data range: rows become columns and columns become rows. For example, if you have monthly sales figures listed down column A (Jan, Feb, Mar) with values in column B, transposing turns them into a row across the top (Jan in A1, Feb in B1, etc.). This is useful when you receive data in a vertical layout but need it horizontally for a report, chart, or database import.

Excel offers two main ways to transpose: the static Paste Special method and the dynamic TRANSPOSE function. The static method creates a one-time copy, while the dynamic function links to the original data, so any changes to the source automatically update the transposed result.

How to Transpose with Paste Special (Static Method)

This is the simplest approach and works in every Excel version.

  1. Select the range of cells you want to transpose (e.g., A1:C5).
  2. Press Ctrl+C (or right-click and choose Copy).
  3. Click the cell where you want the transposed data to start (e.g., E1). Make sure there is enough space – the new range will have the opposite dimensions (5 rows x 3 columns in this example).
  4. Right-click the destination cell, then under Paste Options, click the Transpose icon (it looks like a small grid with arrows). Alternatively, use Paste Special > Transpose from the right-click menu or the Home tab.

On a Mac, the steps are identical, but you can also use Command+C and Command+Option+V to open Paste Special directly.

The result is a static copy – if you change the original data, the transposed version does not update. To get a live link, use the TRANSPOSE function below.

How to Use the TRANSPOSE Function (Dynamic Method)

The =TRANSPOSE() function returns a range that flips rows and columns. It is an array function, so the way you enter it depends on your Excel version.

Assume your data is in A1:C5 (3 columns, 5 rows). To transpose it into E1:I3 (5 columns, 3 rows), follow these steps:

  1. Select the destination range E1:I3 (the opposite dimensions: 5 columns wide, 3 rows tall).
  2. Type =TRANSPOSE(A1:C5) in the formula bar.
  3. Press Ctrl+Shift+Enter in Excel 2019 or earlier. In Excel 365 and Excel 2021, just press Enter – the formula spills automatically.

If you are using Excel 365, you can also use the new =TRANSPOSE(A1:C5) without selecting the destination range first – just type it in a single cell and it will spill. For older versions, you must select the entire output range before entering the formula, and the formula appears with curly braces {=TRANSPOSE(A1:C5)} in the formula bar.

The dynamic version updates automatically when you change values in the original range. However, you cannot edit individual cells in the transposed output – it is a single array. If you need to modify the transposed data, convert it to static values first (copy, then Paste Special > Values) or use the Paste Special method.

Paste Special vs. TRANSPOSE: Which Should You Use?

Choose based on whether you need a live link or a one-time snapshot.

  • Paste Special – Best for a one-time conversion, when you want to edit the transposed cells independently, or when you are working with a small dataset and don’t need automatic updates.
  • TRANSPOSE function – Best for dashboards or reports where the source data changes frequently and you want the transposed view to stay in sync. It also works well with dynamic arrays in Excel 365.

If you need to transpose data that is the result of a formula (e.g., a FILTER result), the TRANSPOSE function is the only practical option, as Paste Special would only copy the current values.

Common Problems and How to Fix Them

Transposing seems simple, but users often hit a few snags.

  • #VALUE! error with TRANSPOSE – This usually happens when you select a destination range that is not the exact opposite size. For example, transposing A1:C5 (3×5) requires a destination of exactly 5 rows by 3 columns. Ensure the selection matches the flipped dimensions.
  • Cannot edit transposed cells – When using TRANSPOSE, the output is an array. To edit individual cells, copy the transposed range and use Paste Special > Values to replace it with static numbers.
  • Data overwritten – If you paste transposed data over existing cells, Excel will overwrite them without warning. Always choose a blank area or insert new columns/rows first.
  • Transpose not available in Paste Special – This can happen if you copy a range that is not contiguous (e.g., you selected multiple non-adjacent areas). Select a single contiguous range before copying.

Alternatives: Power Query and Other Methods

For large datasets or repeated transposing tasks, consider Power Query (Get & Transform Data).

In Excel 365, go to Data > From Table/Range to load your data into Power Query. Then, use Transform > Transpose in the Power Query editor, and load the result back to a worksheet. This method is non-destructive and can be refreshed when the source changes.

Another alternative is to use the TRANSPOSE function combined with other functions like =SORT() or =FILTER() to transpose a filtered or sorted list. For example, =TRANSPOSE(FILTER(A1:C10, B1:B10>100)) would transpose only the rows where column B exceeds 100.

Frequently Asked Questions

Can I transpose data without using a formula? Yes, use Copy > Paste Special > Transpose. It is a one-time static conversion.

Why does my TRANSPOSE formula show #VALUE!? The destination range size is wrong. Select exactly the opposite dimensions of the source range (e.g., 2 rows x 3 columns becomes 3 rows x 2 columns).

How do I transpose a table in Excel without breaking formatting? Paste Special > Transpose copies values and number formatting, but not cell colors or borders. To preserve formatting, apply the formatting to the destination range manually, or use Power Query which can retain some formatting.

Does TRANSPOSE work in Excel for the web? Yes, but you must press Enter (dynamic arrays are supported). In older versions of Excel for the web, you may need to use Ctrl+Shift+Enter.

Can I transpose a single column to a single row? Yes, select the column, copy, then right-click the destination cell and choose Transpose. For a dynamic version, use =TRANSPOSE(A1:A10) and select a horizontal range of 10 cells.

Conclusion

Transposing data in Excel is a common task with two clear solutions: the quick Paste Special method for one-time flips, and the TRANSPOSE function for dynamic, auto-updating results. For advanced users, Power Query offers a robust alternative for complex transformations. Choose the method that fits your need for editability and live updates, and you’ll save time and avoid manual errors.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.