fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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:

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

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