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

Posted on February 4th, 2013 in Excel Howtos - 22 comments

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:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

22 Responses to “Transpose a table quickly using Copy – Paste [Quick tips]”

  1. Hui says:

    Although this is a great tip, you need to beware of potential problems
    If your formula has an = in it eg: =if(A1=1,1,0)
    you will replace both = with X=
    In this case it ‘s probably ok,
    But I’ve seen cases where it can wreck formula
    Also better to choose something that doesn’t occur commonly
    I typically use a ~ so replace the = with a ~
    then follow the same sequence
     

  2. [...] Continue reading here: Transpose a table quickly using Copy – Paste [Quick tips] | Chandoo … [...]

  3. Neeraj says:

    It was awesome.

  4. hpmixco2004 says:

    Awesome!!! Thanks for this treat!!  You make me easy and funny my work!!

  5. phani says:

    Thanks Joey

  6. Sally says:

    Wow this is amazing – and so simple. Thank you so much Joey.
    This will save me so much time – the reports I work with come out with the latest month on the left and working to the right. This might make it easier to see the latest figures right next to the product or customer description, but if you graph using that order, it gives the wrong impression ….
    I have been creating monthly reports, with graphs & tables etc but to get them to display in a logical order has taken many hours of work (and hoping that I have selected the correct cell when creating formula.
    Thank you, Thank you, Thank you.
    Sally

  7. DJ says:

    Thank you Joey. It is a new thing that I learnt today.
    However, I hope we can use Offset fuction in place of it. For example, we have some data in lets say from A1:F6 range and we write a formula =OFFSET($A$1,COLUMN()-9,ROW()-1,1,1) in cell number I1 and drag it in I1:N6 to get transposed data.

  8. Swapnil says:

    hello, its a good technique, however, if i just copy the section(original) & paste special >Value >Transpose, gives me exactly the same output.
    Thanks.

    • Jason says:

      my first thought exactly…. why go through the extra steps instead of just 1)copy, 2)paste-transpose…??

      i must be missing something

  9. Sawan says:

    Awesome

  10. imzde says:

    Hi Chandoo,
     
    JKP has a solution too. (VBA)
    take a look on the link 
    http://www.jkp-ads.com/articles/formulatranspose.asp
    Regards.

  11. Joey says:

    Thanks for all the thanks everybody.  And for the link Chandoo.

  12. Raj says:

    Really Awesome !!!
    Just a query – in Ctrl + Alt + V when the pop-up comes, there is no options of Link (hyperlink), so could not figure out if that’s hidden & there are more such!
    Regards,
    Rajib

    • Hui says:

      @Raj
      Paste Special is content sensitive and so it may be disabled
      If you enter a web address in a cell and copy it Ctrl C
      Then in another cell Ctrl Alt V, Paste Special has the
      All Using Source Theme button enabled
      You can use that to paste Hyperlinks
       

  13. Hossat says:

    Great, thank you!

  14. Spencer says:

    This is great, but I have a different situation today. What if I wanted the above data to look like this:

    East P1 10
    East P2 50
    East P3 90
    West P1 20
    etc etc

    Doesn’t anybody know an efficient way to do this?

  15. […] Transposing a table of formulas quickly using copy-paste. […]

  16. […] again to Chandoo for his superb trick. This is just a replication of his […]

  17. Karanbir says:

    What about using copy and then paste transpose
    This works just as well.(It is simpler though)

  18. Tom says:

    I like it but it took me awhile to get what you were doing. This is because you are transposing a table of formula and NOT data. Perhaps the title should be “Transposing a table of formulae…”. A number of people went down this same track.

Leave a Reply