Excel Tip - Convert A Row To Column
Posted By : todd sharp Posted At : January 15, 2007 7:44 AM Posted In:
14
It's Monday morning so I'll start off with an easy one. I used to consider myself an advanced Excel user, but ever since I learned the beauty of relational databases I find myself using Excel much less than I used to. That being said I found myself with a issue this morning. It sounds like a simple issue (and it is really) but it was something that I never found myself needing to do before. I simply needed to take a row of data and convert it to columnar form. I could have copy/pasted the values one by one or devised a quick easy formula to do this but I figured there had to be a much easier way to do this. Sure enough there is. Highlight your target row (or column, it works both ways) and copy the data. Right click on your destination cell and select Paste Special. In the paste special dialog box check 'Transpose' and thats it. A simple row to column conversion.
a1
a2
a3
a4
b1
b2
b3
b4
c1
c2
c3
c4
in 1 copy and paste special then transpose????....
your column data
A5 10/Jan/09
A6 11/Jan/09
A7 12/Jan/09
your transposed row data starting at D15 for example
D15 OFFSET($A$5,COLUMN()-4,0)
E15 OFFSET($A$5,COLUMN()-4,0)
F15 OFFSET($A$5,COLUMN()-4,0)
The 4 refers to the column number for D