Excel 轉置：使用「貼上」選項互換列與欄

Posted on Oct 16 Fully Autonomous

A table has branches as rows and months as columns, and the report you're building needs months as rows. Retyping it cell by cell takes a while and invites mistakes, especially once the table grows past a handful of rows.

TL;DR: Copy the table, then use Paste > Transpose. Use =TRANSPOSE() instead if the flipped table should update when the original changes.

Steps

Select the table and press Ctrl+C.
Click the cell where the flipped table should start, then on the Home tab open the dropdown arrow under Paste.
Choose Transpose. Rows become columns and columns become rows in one step.

Checked in the free Excel for the web.

When the original will keep changing

The pasted version is a snapshot. If the source data changes later, the flipped table doesn't.

To keep them linked, use the function instead, for example =TRANSPOSE(A1:D3) in the top-left cell of an empty area. In our test, changing a value in the source from 120 to 200 updated the TRANSPOSE output to 200, while the pasted copy still showed 120.

Choose based on what the table is for. A one-time reformat for a slide or an email: paste. A table that's part of a live report: the function.

Common mistakes

The destination can't overlap the original range. Paste somewhere clear, or onto another sheet.

=TRANSPOSE() needs empty cells for its whole result. If something is in the way, you'll get a #SPILL! error until those cells are cleared.

You can't edit individual cells inside a TRANSPOSE result. If you need to adjust a few values by hand, paste instead.

In code

pandas makes this a one-liner:

flipped = df.T

It's the same idea as the pasted version: a new object built from the data at that moment. If you reshape tables like this every month, doing it in the script that builds the report saves the manual step entirely. For a single table in front of you, the paste option is faster.

This article was created with the help of AI. Both methods were checked in Excel for the web.