Impossible 'Excel' question??

  Housten 17:07 13 Mar 12

Good Afternoon,

I know a small amount about excel but I think what I want to do is impossible, BUT if someone can prove me wrong I would be very, very happy.

What has happened I created a spreadsheet some time ago and the data I have entered, and will continue to enter goes down the spreadsheet, 'vertically' shall I say. I have created a second sheet and realised that some of the data I wanted was actually on the first sheet, so I thought it would be easy to copy. WRONG!! The ata on the second sheet is 'horizontal'! So what I am trying to do is to copy information from, say, c4 to c300 into the second sheet in c23, d23, e23, f23...... So what I need is to be able to make 'Name$C4' in c23 [ which I can just copy in as it is the first ], and then copy that into d23 which will have 'Name$C5' - by some instruction to change/add to the formula, and then in e23 have it equal 'Name$C6'. As I said I think that this can not be done but.......Excel has some very interesting instructions within it and I am just wondering if this change could be one of them. If anyone knows how it can/could/can not be done I will be very grateful. To be honest a negative answer will be useful, it will mean spending some time re-doing the sheet, but I will at least know it is impossible.

Many thanks to any and all who reply.

  bremner 17:21 13 Mar 12

Use copy and then paste into second sheet using paste special chosing Transpose from the menu.

  Zak 17:59 13 Mar 12

This will provide you with the formulas, but you must allow the array to include for further entries e.g C4:C1000; once set you cannot change without re-doing the process of setting up the column array.

Excel TRANSPOSE Function

  lotvic 18:11 13 Mar 12

Tip: Use Sheet 3 (a blank sheet) to Paste Special Transpose into then you won't mess up the original sheet.

Paste Special Transpose a couple of links

Rotate data by converting columns to rows (or vice versa)

paste copied cells in a transposed direction

  Zak 21:37 13 Mar 12


Add to my entry above:

You will also need to ensure your selection of the row on sheet 2 also allows for further entries e.g. C23, C24, ..........C1023; otherwise the formula will not pickup the values that you will be entering in the future.


  Housten 12:04 14 Mar 12


bremner, Zak and lotvic,

Many, many thanks for all your speedy replies. I will now have to settle down and work out how to use your replies and incorporate them into my spreadsheet. Just a matter of time, I assure you. We pensioners take that little longer to get our heads round new things like this, but get my head round it I will. I can see me having further uses of it as well, so I am very, very grateful to all of you!

many,m many thanks again, because I can not emphasise how useful this will be to me.


This thread is now locked and can not be replied to.

Honor 5X review: Hands-on with the new budget phone from Hauwei's young brand

1995-2015: How technology has changed the world in 20 years

You can now publish data visualisations to the web from Power BI

Complete guide to Apple's campus 2, everything we know about the new spaceship HQ, plus: new drone…