Transpose a table quickly using Copy – Paste [Quick tips]

On Friday, we learned how to transpose a table of data using Excel formulas.

In comments of that post, Joey gave us an interesting tip.

Might I recommend an alternative that involves no array formulas, is easier to debug and less computationally intensive.
1) Highlight area to be transposed and copy
2) Paste special -> Paste Link, somewhere else on the sheet
3) Highlight new area and Find/Replace “=” with “xxx”
4) Copy new area, paste special –> transpose
5) Find/Replace “xxx” with “=”
Now you have a direct link to the cell with no fancy formula required (link)

This is quick, easy to use & lovely way to transpose data.

So let me explain this in detail.

6 Steps to transpose a table of data

by using Copy, Paste, Find & Replace

1. Copy your original data & paste links to it in a blank place (CTRL+ALT+V and L)
2. Select this new data & Press CTRL+H
3. Replace all = with x=
4. Copy again & paste special > transpose in another blank area
5. Press CTRL + H again and Replace all x= with =
6. You are Done!

See below tutorial to understand how this works:

Thanks Joey…,

Special thanks to Joey for suggesting this method. It is a testament to our readers’ awesomeness.

If you enjoyed this tip, say thanks to Joey.

More tricks using Paste Special, Find Replace etc.

For a few more tricks & clever uses of paste special, find replace, go to special, check out these links:

• Quickly fill blank cells in a table using Paste Special
• Extract portions of text using Find Replace
• Use Paste Special to format charts quickly
• Quickly change formulas using Find Replace
• Compare row differences using Go to Special
• Delete Blank rows using goto special
• More tips on using Paste Special, Go to Special & Find Replace