How to Transpose Rows and Columns in Excel

The "wide table to tall table" problem

You exported quarterly data with quarters as columns. The next system wants quarters as rows. Excel needs to flip the orientation. This operation is called a transpose. It's basic, common, and Excel has multiple ways to do it. Here's how to pick the right one.

TL;DR — Key takeaways

• Paste Special > Transpose is the standard one-shot method. Good for static snapshots.
• The =TRANSPOSE() function stays live (the transposed view updates when the source changes) but requires array-formula entry on older Excel.
• Power Query's Unpivot handles wide-to-tall reshape elegantly when you need recurring refreshes.
• A free VBA macro does Paste Special > Transpose with two clicks: select source, click destination, done.

Method 1: Paste Special > Transpose

The classic.

1. Select the source range.
2. Ctrl + C to copy.
3. Click the destination cell (top-left of where the transposed result should land).
4. Right-click > Paste Special.
5. Check Transpose, click OK.

Result: rows become columns and vice versa. The original is left in place.

Pros: Static result. Doesn't depend on the source.

Cons: Three steps that get awkward when you've forgotten which one comes first. The destination cell choice has to be exactly right or you overwrite something else.

Method 2: =TRANSPOSE() function

Excel's built-in TRANSPOSE function returns a transposed array.

=TRANSPOSE(A1:F10)

Older Excel: enter as an array formula with Ctrl + Shift + Enter.

Newer Excel (Microsoft 365, Excel 2021+): just type and press Enter; dynamic arrays handle the spill.

Pros: Live. The transposed view updates when the source changes.

Cons: The result is a formula, not values. Copy-paste-as-values to lock it. Old Excel requires array-formula syntax. Not all environments handle dynamic arrays the same way.

Method 3: Power Query Unpivot (for wide-to-tall reshapes)

If your "transpose" need is actually "convert wide table to tall list" (one row per category-value combo), Power Query is the right tool.

1. Load the data into Power Query.
2. Select the columns to unpivot (the value columns, like quarters).
3. Transform > Unpivot Columns.
4. Power Query produces a tall table with one row per category-quarter combo.
5. Close & Load.

This is a different operation than transpose, but often what users mean when they say "transpose": they want the wide layout flattened into a vertical list.

Pros: Refreshable. Right shape for pivot or chart consumption.

Cons: Setup time. Different result than a pure transpose.

Method 4: The free VBA macro (two clicks)

Download Transpose Rows and Columns. Free .xlsm with one macro.

Steps:

1. Select the source range to transpose.
2. Press Alt + F8, pick TransposeRowsAndColumns, click Run.
3. When prompted, click the top-left cell where the transposed result should appear.
4. Done. The transposed copy lands at your destination.

Two clicks. The original data stays in place so you can keep both layouts side by side.

A common scenario: budget review with wide-to-tall

CFO sends you last year's actuals as a wide table: 40 stores down, 12 months across. The planning tool wants tall: one row per store, month, actual combo (480 rows total).

Step 1: transpose so months are down and stores are across:

• Use the macro: select source, click destination, done.

Step 2: flatten so each store-month combo is its own row:

• Either Power Query Unpivot, or write INDEX/MATCH formulas, or copy-paste each store's column as a vertical block.

The macro handles step 1. Step 2 needs a different approach.

What about formulas?

The macro uses xlPasteAll, which preserves formulas. After transpose, formula references shift in the same way they would for a normal copy-paste. A formula =A2+B2 in row 5 of the source ends up in row 1 of the transposed result, still as =A2+B2 (which now references different cells). Almost always you want values-only after transpose.

To get values-only:

1. Run the transpose.
2. Run Convert Formulas to Values on the transposed result.

Or edit the macro source to use xlPasteValues instead of xlPasteAll.

What about formatting?

xlPasteAll includes formatting. Cell colors, borders, number formats all transpose along with the data. Headers stay bold. Currency stays currency. If you want plain values without formatting, edit the .bas source to swap xlPasteAll for xlPasteValues.

What about merged cells in the source?

Merged cells in the source range cause the transpose to behave unpredictably (Excel's built-in limitation, not a macro problem). Run Unmerge Cells and Fill Down first to flatten the merges, then transpose safely.

Frequently asked questions

Can I transpose more than one range at a time?

No. The macro processes one selection at a time. For multiple ranges, run it once per range.

What if the destination overlaps with the source?

The transposed result overwrites whatever's at the destination, including the source if they overlap. Always pick a destination with empty space to the right and below.

Does it work for very large ranges?

Yes, but Paste Special has practical limits. For ranges over 100,000 cells, the operation takes a few seconds. For ranges over 1,000,000 cells, you might hit memory issues. In those cases, Power Query Unpivot is more efficient.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running. The macro doesn't modify the source, so undo just removes the transposed copy. The original is untouched either way.

What about transposing a vertical list back to horizontal?

Same operation, different direction. The macro doesn't care about the source orientation: a 1-by-N row becomes an N-by-1 column, and vice versa.

What to do next

If you also need to reverse the row order (for a chronological flip), see Reverse Row Order. For a transposed result you want to share without live formulas, Convert Formulas to Values freezes the transpose into static values.