Using Transpose in Excel: Methods, Formulas, and Tips

Formulas & functions · Updated November 20, 2026

To transpose data in Excel, copy the cells, select the destination, and choose Home > Paste > Transpose. For a result that updates when the source changes, enter a TRANSPOSE formula such as =TRANSPOSE(A1:C2) in a blank cell; in Microsoft 365, the results spill into the required rows and columns automatically.

What transposing data means

Transposing switches a range’s rows and columns. A horizontal row becomes a vertical column, and a vertical column becomes a horizontal row. For a rectangular range, the number of rows and columns swap: a range with two rows and three columns becomes one with three rows and two columns. The values retain their relative order.

This is useful when information is arranged in a way that does not suit the report or analysis you need. For example, a worksheet might list months down the left side, while a chart or summary needs months across the top. Transposing can also make a wide table easier to print, or turn a list of field names across columns into a vertical checklist.

Transpose changes the layout, not the meaning of the data. It does not sort, summarize, or clean values. Decide whether the destination should be a one-time copy or a live reflection of the source before choosing a method.

Choose between Paste Special, a formula, and Power Query

Excel offers several ways to transpose, and the best one depends on how the result should behave:

  • Paste Special > Transpose: Best for a quick, independent copy. The pasted values do not automatically change when the original range changes.
  • TRANSPOSE formula: Best when the transposed result should update as source values change. In current Microsoft 365 Excel, the formula spills into adjacent cells.
  • Power Query: Useful when you need a repeatable data-import or reshaping process that can be refreshed. It is more setup than a one-time paste.

For a small range that needs to be rearranged once, Paste Special is usually simplest. For a dashboard or report linked to changing inputs, use a formula. If the source is an imported dataset that you regularly refresh, consider Power Query.

Transpose with Paste Special

In Excel for Microsoft 365 on Windows, use these steps to create a transposed copy:

  1. Select the source range and press Ctrl+C.
  2. Click a blank destination cell outside the source range.
  3. Go to Home > Paste, open the Paste menu, and choose Transpose. Depending on the ribbon layout, Transpose may appear as a button or an icon in the paste options.

You can also right-click the destination and choose the Transpose paste option. If you open Paste Special, select the Transpose checkbox before confirming. On Windows, Ctrl+Alt+V opens the Paste Special dialog; the available keyboard navigation can vary with the dialog and Excel build, so the ribbon or context menu is the clearest route.

Excel places the copied cells in the swapped orientation. For example, if the source is three rows by two columns, the destination will be two rows by three columns. Leave enough empty space for the full result. Pasting into occupied cells can replace their contents, so choose the destination carefully.

On Excel for Mac, copy the range, select the destination, and use the Transpose option from the Home tab’s Paste menu or the context menu. Ribbon organization can differ by version, but the operation is the same. Paste Special Transpose creates a snapshot: editing the source later will not update the pasted copy.

Use the TRANSPOSE function for a live result

The TRANSPOSE function returns a range in the opposite orientation. In Microsoft 365 Excel, place the formula in the top-left cell where you want the output to begin. Excel determines the output dimensions and spills the result into neighboring cells.

With the source data in A1:C2 and the intended output beginning in E1, enter =TRANSPOSE(A1:C2) in E1. The two source rows and three source columns become three output rows and two output columns, occupying E1:F3. Change a source value and the corresponding spilled result updates.

Do not type the formula separately into every output cell in a current version of Excel. It is a dynamic array formula, so the formula appears in the upper-left cell and the other result cells are part of its spill range. If the output area is blocked by content, Excel displays a #SPILL! error. Clear the obstructing cells or move the formula to a larger empty area.

The output is linked to the source values, but the formula does not copy all source formatting. Apply number formats, borders, or other presentation choices to the destination separately. Also, a spilled formula cannot be placed inside an Excel table; enter it in the worksheet grid outside the table.

Older Excel versions and array formulas

Earlier Excel versions that do not support dynamic arrays use TRANSPOSE as a traditional array formula. First select the full destination range, with its dimensions reversed from the source. Then type the function and confirm it as an array formula rather than pressing Enter alone.

For a source in A1:C2 and a destination selected as E1:F3, enter =TRANSPOSE(A1:C2), then press Ctrl+Shift+Enter on Windows. Excel places the formula in the selected cells as one array formula. On Mac, the traditional array-formula shortcut is Control+Shift+Enter, though keyboard behavior may depend on the keyboard and Excel version.

In these older versions, the destination range must be selected at the correct size before entering the formula. You generally edit or delete the array formula as a whole, rather than changing just one result cell. If the result should be static instead, use Paste Special > Transpose, which does not require an array formula.

Formatting, blanks, and common problems

Transposing values does not guarantee that the result will look exactly like the source. Paste Special can offer options that include formatting, but a formula returns calculated values and does not reproduce cell colors, borders, column widths, or every other layout choice. Check the destination’s number formats, especially for dates, percentages, and currency.

Watch for these common issues:

  • There is not enough room: A formula needs an unobstructed spill area. Remove any content from the cells where the output should appear.
  • The destination is inside a table: Dynamic array formulas cannot spill within an Excel table. Put the formula in a normal worksheet range outside it.
  • The result does not change: A Paste Special result is a copy, not a live link. Use a TRANSPOSE formula if source changes must flow through.
  • Some values look different: Review number formats and widths. A date may display as a serial number if the destination uses General formatting, for example.
  • The copied cells include formulas: A paste may carry formulas whose references adjust according to Excel’s normal copy rules. Check the results if formulas, rather than fixed values, are part of the source.

Blank source cells can also be surprising when a formula references them: Excel may display a zero in the transposed result. If blanks must remain visually empty, decide how you want to handle them and test the result on a small sample before using it in a report. A static paste may be more appropriate when preserving the visible values matters more than maintaining a live link.

Transpose data with Power Query

Power Query can transpose data as one step in a repeatable import and transformation process. This is useful when you receive a similar file on a schedule and want to refresh the reshaped output rather than repeat a manual copy-and-paste operation. Power Query is available in Excel for Microsoft 365 on Windows; Mac availability and capabilities depend on the Excel version, so check the Data tab in your edition.

For a worksheet range, first make sure the data has clear headers and select a cell in it. On Windows, use Data > From Table/Range. If Excel asks whether to create a table, confirm the range and whether it has headers. In the Power Query Editor, choose Transform > Transpose. When the preview is correct, use Home > Close & Load to return the result to Excel.

Power Query treats the transposition as a transformation in a query, rather than as a live cell-by-cell formula. To bring in changed source data, refresh the query. Because a transpose can move the original header row into the data area, inspect the preview and promote headers or adjust the query when needed. This method is especially helpful for imported files, but it is unnecessary overhead for a one-time rearrangement.

A practical example: turning month rows into columns

Suppose A1:B4 contains a two-column list: the first column has Month, January, February, and March, while the second has Sales, 1200, 1350, and 1280. If a report needs the month names across a row and sales figures beneath them, transposing the whole range produces a two-row, four-column layout. The labels move with their values, so January remains paired with 1200, February with 1350, and March with 1280.

For a report that is rebuilt from changing source values, place a TRANSPOSE formula in an empty area and verify the output dimensions. For a final presentation that should not change when the original list is edited, use Paste Special instead. If the list arrives repeatedly as a file, Power Query can make the layout change part of a refreshable workflow. In each case, check that headers, labels, and number formats still make sense after the orientation changes.

Frequently Asked Questions

How do I transpose rows and columns in Excel quickly?
Copy the range, select a blank destination, and choose Home > Paste > Transpose. This creates a separate copy that will not automatically update with edits to the source.

Does the TRANSPOSE formula update automatically?
Yes. In Microsoft 365 Excel, a TRANSPOSE formula updates when its source cells change, provided calculation is enabled and the spill range is not blocked. The formula result reflects source values, not source formatting.

Can I transpose data without replacing the original?
Yes. Paste the transposed copy into a blank area of the same sheet or another worksheet. A TRANSPOSE formula also leaves the source unchanged, as long as you place its output elsewhere.

Why does my TRANSPOSE formula show #SPILL!?
Excel cannot place the full result because one or more cells in the required output area are occupied, merged, or otherwise preventing the spill. Clear the area or move the formula. In older Excel, the issue may instead be an incorrectly selected array-formula range.

Can I transpose an Excel table?
Paste Special can transpose copied table cells, but Excel tables do not support a dynamic array formula spilling inside the table. Put a TRANSPOSE formula in a normal worksheet range, or use Power Query for a repeatable transformation.

Will transposing copy formulas and formatting?
Paste behavior depends on the chosen paste option, and formulas may adjust their references as they are copied. A TRANSPOSE formula returns values from its source but does not reproduce the source’s full formatting. Review formulas and formats after either method.

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.