Excel Table: How to Flip Rows and Columns

How-to basics · Updated November 18, 2026

To flip an Excel table so its rows become columns and its columns become rows, copy the range, choose a blank destination, then use Home > Paste > Transpose. This creates a one-time, static result; use the TRANSPOSE function if the flipped output should update when its source changes.

What flipping rows and columns means

In Excel, this operation is called transposing. It changes the orientation of a rectangular range: the first row becomes the first column, the second row becomes the second column, and so on. A range that is four columns wide and three rows high becomes three columns wide and four rows high.

For example, a small sales range might have products down the rows and months across the columns. After transposing, the months can run down the rows and each product can have its own column. The cell values move into corresponding positions; the operation does not sort the data, summarize it, or reverse the order of rows.

There is an important distinction between an Excel table and a range of cells. An Excel table is a structured object with features such as filter buttons, named columns, and automatic expansion. Transposing the cells does not rotate that table object in place. The usual workflow is to transpose the data into a normal range and, if needed, make a new table from the result.

Transpose a table with Paste Special

Use Paste Special when you need a quick snapshot and do not need the output to stay linked to the original. In Excel for Microsoft 365 on Windows:

  1. Select the full source range, including its headers, and press Ctrl+C.
  2. Click a blank cell outside the source range. Leave enough empty space for the flipped result; its row and column counts will be exchanged.
  3. On the Home tab, open the Paste dropdown and select Transpose. In some layouts, Transpose appears among the Paste Options after you copy the cells.
  4. Check the result, especially its headers and any formulas, before using or sharing it.

You can also open the Paste Special dialog with Ctrl+Alt+V, select Transpose, and confirm. On Mac, copy with Command+C, then use the Paste dropdown on the Home tab or Edit > Paste Special and choose Transpose; the precise menu presentation can vary by Excel for Mac version. The result is independent of the source: editing the original cells later will not update the transposed copy.

Paste Special can carry cell formatting along with the copied cells, but do not assume it preserves the original Excel table as a functioning table. Features such as the source table’s filters, structured references, and automatic expansion do not become a rotated table. If you want a clean result, paste first into an empty area and inspect the formatting rather than pasting over existing data.

Use TRANSPOSE for an updating result

For a live output, enter the TRANSPOSE function in a blank cell outside any Excel table. In Microsoft 365, the formula spills into the required number of rows and columns automatically. For example, suppose A1:C3 contains Product, Jan, and Feb in its first row, followed by Tea with values 10 and 12, and Coffee with values 8 and 11. With that layout in A1:C3, enter this formula in E1:

=TRANSPOSE(A1:C3)

The result in E1:G3 is Product, Tea, Coffee across the first row; Jan, 10, 8 across the second; and Feb, 12, 11 across the third. If a source value changes, the displayed result updates. The formula does not copy the source cell formatting, so format the output separately if you need a particular number format, fill, or border.

In current Microsoft 365 Excel, enter the formula once and let the dynamic array spill. Excel 2021 also supports dynamic arrays. Older Excel versions may require selecting the entire destination area and confirming TRANSPOSE as an array formula with Ctrl+Shift+Enter; behavior depends on the version. If the spill area contains existing content, Excel displays a #SPILL! error. Clear or move the obstructing cells, and make sure the destination is not inside an Excel table: dynamic-array spill formulas are not supported within table cells.

Turn the transposed result into a new Excel table

If you need filters, banded rows, or structured references on the flipped data, create a new table after transposing. First make sure the result has a single header row and that each header is usable. Then select the complete result, including its headers, and choose Insert > Table. Confirm the range and check My table has headers if the first row contains field names.

Excel table column names need to be distinct. A transpose can put values that were previously row labels into the new header row, so inspect those values before creating the table. If the new headers are blank or repeated, rename them to clear, unique labels. Also check whether the first source row really represents headers: transposing a range with no header row can make ordinary data look like table headings.

When the output needs to stay connected to a changing source, keep the TRANSPOSE formula in a normal worksheet range and do not try to wrap the spill area in a table. If downstream work specifically requires a refreshed table, Power Query is often a better fit because it can load a transformed result as a table.

Transpose repeatedly with Power Query

For a recurring task, Power Query can record the transformation so you can refresh it instead of repeating copy and paste. On Windows, select a cell in the source table and choose Data > From Table/Range. In the Power Query Editor, choose Transform > Transpose. Review the preview, use the first row as headers if the transformed layout requires it, and choose Home > Close & Load to return the result to Excel.

Power Query is useful when the source is replaced or updated on a regular schedule, or when several preparation steps accompany the transpose. It produces a query output rather than a live cell-by-cell formula link; refresh the query to bring in source changes. Confirm that the query points to the intended source and that header promotion is correct. If the number or arrangement of source columns changes, review the refreshed output because the transformed shape may change too.

Choose the method that fits the job

  • Paste Special: Best for a quick, one-time rearrangement. It is easy to use, but later edits to the source do not flow into the pasted result.
  • TRANSPOSE: Best when a worksheet view should update as source cells change. It needs a clear spill area and returns cell values, not a rotated table object or copied formatting.
  • Power Query: Best for a repeatable import or preparation process. It requires a refresh and a little setup, but can combine transposing with other data-cleaning steps.

Think about what should happen after the first conversion. If the source is a one-off report, static paste is usually simpler. If it is a dashboard view of cells that will continue changing, a formula is more appropriate. If new data arrives repeatedly and the same transformation must be applied each time, a query is easier to maintain than a manual sequence of pastes.

Common problems and checks

  • The output is blocked: A dynamic TRANSPOSE result needs an empty destination rectangle. Clear cells, merged cells, or other content in the spill area to resolve a #SPILL! error.
  • The result is not updating: Paste Special makes a static copy. Replace it with a TRANSPOSE formula or refresh the Power Query output, depending on the method used.
  • Formatting looks different: A formula returns values but not the source’s full formatting. Apply number formats and styles to the output separately.
  • Formulas show unexpected references: A transposed paste can carry formulas, and relative cell references may adjust as they do during a paste. Inspect important formulas; paste values instead if you only want their current results.
  • The headers are unsuitable: A transpose changes which values appear in the first row. Rename blanks or duplicates before making the result into a table.

Another common misconception is that transpose reverses the order of a list. It does not. It changes orientation while preserving the sequence, so the first source row or column remains first in the corresponding destination direction. If you want to reverse an order as well, that is a separate sorting or indexing task.

Frequently Asked Questions

Can I flip an Excel table without copying it?
Not as an in-place rotation of the Excel table object. Transpose the cells into a separate blank range, then create a new table if required. Keep the original until you have checked the result.

Does TRANSPOSE update automatically?
Yes, a TRANSPOSE formula in a dynamic-array version of Excel recalculates when its referenced source cells change. It needs a clear spill area, and its output cannot spill inside an Excel table.

Will transposing keep my formatting?
Paste Special may carry some cell formatting, but a TRANSPOSE formula does not reproduce the source formatting. Neither method turns the original table’s filters and structured-reference behavior into a rotated table.

Can I transpose only part of a table?
Yes. Select just the rectangular cells you want to rearrange, including or excluding headers as appropriate, and transpose that range. Check the new first row before converting the output to a table.

Can I transpose a table and keep formulas linked?
Use TRANSPOSE with a source range if you want the displayed values to recalculate from the source. For a repeatable data-import workflow, use Power Query and refresh it. A Paste Special transpose is a snapshot, not a continuing link.

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.