Transpose Rows and Columns

What it does

You have data with quarters (or months, or product names) running across the columns. You need them down the rows instead so you can pivot, chart, or feed it into a system that wants a tall layout. Excel's Paste Special > Transpose works, but you have to copy the source first, click the destination, then dig through the menu. The macro does it as a single click-and-pick: select the source, run the tool, click the destination cell, done. The original data stays in place so you can keep both layouts side by side.

Real-world example

Picture an FP&A analyst at a regional retail chain working on the annual budget review. The CFO sent her last year's actuals as a wide table: rows are stores (40 of them), columns are months (Jan through Dec). She needs to feed it into the planning tool, which wants it tall: one row per store-month combination. So 40 rows times 12 columns becomes 480 rows of store, month, actual.

Her current method: copy the wide table, paste-special-transpose into a fresh tab, then write =INDEX/MATCH formulas to flatten the now-tall-but-still-wide table into a single column. About 25 minutes of work and three cells of formula gymnastics that she hates because she keeps swapping the row and column references.

With this macro the first half of that workflow is one click. She selects the 40-row by 12-column block, runs the tool, clicks the destination cell, and a 12-row by 40-column transposed block appears next to the original. The transpose is 1 second. She still needs to flatten the result for the planning tool, but the transpose step that used to confuse her is now a non-issue.

On a 40-store by 12-month budget review, expect about 5 minutes saved on the transpose step versus copy-paste-special. The bigger win is the lower cognitive load: one click instead of "remember to copy first, remember to use Paste Special, remember to check Transpose."

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the source range you want to transpose. Include headers if you want them transposed too.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick TransposeRowsAndColumns, click Run.
3. When prompted, click the top-left cell where the transposed result should appear. This can be on the same sheet or a different one.
4. The macro writes a transposed copy at that destination. The original data is left in place.

FAQ

Does it preserve formulas?

Formulas are pasted as formulas in the transposed result. Because relative references shift when you transpose (a formula referencing the cell to its left now references the cell above it), the result may not be what you want. To get the transposed values without the formula confusion, freeze formulas first with Convert Formulas to Values, then run the transpose.

What about formatting like fonts, colors, and borders?

The macro uses xlPasteAll, which copies values, formulas, and formatting. So colors, borders, and number formats all transpose along with the data. If you only want values without formatting, edit the .bas source and change Paste:=xlPasteAll to Paste:=xlPasteValues.

Can I transpose more than one block at a time?

No, the macro processes one selection at a time. For multiple blocks, run the macro once per block. Each transpose takes a couple of seconds.

What if the destination cell has data in it?

The transposed result overwrites whatever was at the destination. So if your destination cell is in the middle of an existing table, that table gets clobbered to the right and below. Always pick a destination cell with empty space to the right and below it.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running undoes the paste at the destination. Because the macro doesn't modify your source data, undo cleanly removes the transposed copy without affecting the original. The macro runs entirely offline. Your data never leaves your machine.