Excel Transpose Table: Switch Rows and Columns

How-to basics · Updated November 22, 2026

To transpose a table in Excel, copy the data, then right-click the destination cell and choose Paste Special > Transpose (or press Ctrl+Alt+V, then E). This flips rows into columns and columns into rows, so a 3-row by 4-column table becomes a 4-row by 3-column one. For a dynamic version that updates when the original data changes, use the TRANSPOSE function as an array formula.

What Does Transposing a Table Mean?

Transposing is the process of swapping the orientation of your data. Think of a table with months in the first column and sales figures across the top. After transposing, the months appear across the top row, and the sales figures run down the first column. The actual cell values stay the same; only their positions change. This is different from sorting or filtering, which reorder data within the same orientation.

How to Transpose with Paste Special (Static)

The paste-special method is the quickest for a one-time flip. Here are the exact steps for Excel 365 on Windows:

  1. Select the entire table range (e.g., A1:D4).
  2. Press Ctrl+C to copy (on Mac, Cmd+C).
  3. Click the top-left cell of the destination area (make sure it has enough empty space below and to the right).
  4. Right-click and choose Paste Special (or press Ctrl+Alt+V).
  5. In the dialog, check the Transpose box at the bottom right, then click OK.

On a Mac, the Paste Special dialog is opened with Cmd+Ctrl+V. In Excel for the web, right-click the destination cell, then under Paste Options select the Transpose icon (it looks like a small table with arrows). The pasted result is static—if you change a value in the original, the transposed copy will not update.

How to Transpose with the TRANSPOSE Function (Dynamic)

If you want the transposed table to automatically reflect changes in the original, use the TRANSPOSE function. This is an array formula, so you must enter it with Ctrl+Shift+Enter in older Excel versions, but in Excel 365 it works with a normal Enter due to dynamic arrays.

Example: Suppose your data is in A1:C3 (3 rows, 3 columns). To transpose it starting at cell E1:

  1. Select the range E1:G3 (same number of cells, but rows and columns swapped—here 3 columns and 3 rows).
  2. Type =TRANSPOSE(A1:C3).
  3. Press Ctrl+Shift+Enter (Excel 2019 and earlier) or just Enter (Excel 365).

The result will fill the selected range. Note that you must select the exact size of the transposed range; if you select too many cells, the extra ones will show #N/A errors. In Excel 365, you can just enter the formula in one cell and it will spill automatically.

Choosing the Right Method: Static vs. Dynamic

Which method you choose depends on whether you need the transposed data to stay in sync with the original.

  • Use Paste Special when you want a one-time snapshot—for example, to create a report that won’t change, or to send a flattened version to a colleague.
  • Use TRANSPOSE when the source data is frequently updated, and you want the flipped table to always match. This is ideal for dashboards or live summaries.
  • If you need to edit the transposed data independently (change values, add columns), paste special is better because the TRANSPOSE result is read-only—you cannot modify individual cells in the array.

Common Problems and How to Fix Them

Transposing can sometimes produce unexpected results. Here are the most frequent issues and their solutions:

  • #N/A errors in the TRANSPOSE result: This usually means the selected output range is too large. Re-select the exact transposed size, or in Excel 365, delete the extra cells and let the spill handle it.
  • Formulas in the original data: Paste Special Transpose copies the values of formulas by default. If you want the formulas themselves to be transposed (and adjusted), you need to use the TRANSPOSE function, but note that it will not adjust relative references—it will return the same values as the original, not recalculated ones. For a true formula transpose, you’d need a more complex approach like using INDIRECT with row/column math.
  • Blank cells: Transposing a range with blank cells works, but blanks become empty cells in the result. If you want zeros instead, you can wrap the TRANSPOSE in IF to replace blanks.
  • Merged cells: Transposing merged cells can cause errors. Unmerge the source before transposing, then re-merge after.

Transposing with Power Query (For Large or Repeated Tasks)

If you frequently need to transpose data from external sources, Power Query offers a more robust solution. Here’s how:

  1. Select your data range and go to Data > From Table/Range (this opens the Power Query editor).
  2. In the editor, go to the Transform tab and click Transpose.
  3. Optionally, you can promote headers (use Use First Row as Headers) after transposing.
  4. Click Close & Load to put the result back into a worksheet.

Power Query creates a query that can be refreshed if the source changes. This is especially useful when the data comes from a database or a CSV file that you update regularly.

Alternative: Using INDIRECT for a Dynamic Transpose Without Array Formulas

For users on older Excel versions who are uncomfortable with array formulas, a non-array alternative is to use INDEX and TRANSPOSE? Actually, the classic alternative is to use INDIRECT with cell references. For example, if your data is in A1:C3, you can put this formula in cell E1 and drag it down and right:

=INDEX($A$1:$C$3, COLUMN()-4, ROW())

Assuming you start at E1, COLUMN()-4 gives 1 for column E, and ROW() gives 1 for row 1, so it returns A1. Dragging down increments the row, and dragging right increments the column, effectively swapping the indices. This formula is dynamic and doesn’t require Ctrl+Shift+Enter, but it is more complex to set up and less readable than TRANSPOSE.

Frequently Asked Questions

Can I transpose a table without copying and pasting?

Yes, you can use the TRANSPOSE function as described above, which creates a live link. Another option is to use Power Query, which lets you transpose without copy-paste and can be refreshed. For a one-time conversion, copy-paste is still the simplest.

Why does my transposed table show #N/A errors?

This happens with the TRANSPOSE function when the selected output range is larger than the transposed size. For example, if your original is 2 rows by 3 columns, the transposed result is 3 rows by 2 columns. If you select a 4×4 range, the extra cells will show #N/A. In Excel 365, just select one cell and let it spill, or delete the extra cells.

Does transposing copy formatting and formulas?

Paste Special Transpose copies values and number formatting, but not cell colors or borders by default. To copy formatting, use Paste Special > All and check Transpose—that copies everything, but formulas are pasted as values. The TRANSPOSE function only copies values, and formatting is not applied.

Can I transpose a table in Excel for the web?

Yes. In Excel for the web, copy the data, right-click the destination, and under Paste Options choose the Transpose icon. The TRANSPOSE function also works in the web version, but you must enter it as a regular formula (it spills automatically).

Is there a way to transpose and keep the original formatting?

Yes, use Paste Special and choose All (or Formats plus Transpose). This will copy the cell styles, but note that column widths and row heights are not transposed—you’ll need to adjust them manually.

What if I want to transpose only part of a table?

Just select the specific range you want to flip before copying. For example, to transpose only the first two columns, select those cells and paste special with transpose. The rest of the table remains unchanged.

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.