Ads 468x60px

Wednesday, February 6, 2013

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

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:
Transpose a table of data quickly using Copy, Paste, Find & Replace

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:

0 comments:

Post a Comment