Maybe someone can help me out.
I’m moving information from one spreadsheet to another, and I have a row of data that I need to turn into a column. Aside from turning my head on its side, or manually copying it, is there an easy way to format this?
Yes. Under the edit menu, Paste Special, then Transpose.
Copy the row, then select Paste Special… Choose Transpose from the options.
Perfect! Thank you both for the quick responses.
Just a teeny tip. Excel has a really good help function. Choosing “Help” and then searching on “row to column” instantly will bring up the answer to your question, with examples. I use Help a LOT - it saves quite a bit of frustration and agony.
I have the exact opposite opinion of Excel’s help function. More often than not, my query brings up too many unrelated topics no matter how I try to phrase the question (or enter the appropriate key words). Googling my question often yields much better results.
Using the help often seems like a futile endeavor. I searched for help topics for rows, and help topics for columns, and didn’t locate anything like the transpose function. I blame the Excel/help file version at work as I just searched for ‘rows’ on my copy of Excel 2003 at home and came up with ‘rows to columns’ on the first hit.
Thanks for the mildly condescending tip nonetheless.
Hold on Hoss. I see nothing condescending about that answer.
A better perspective is that Excel has a generally better-than-average help section. There many software packages where you need help to use Help. Excel excels in their Help section. The documentation for this particular product is pretty damn good.
Compare this to Adobe’s Help. Adobe’s is pretty lousy in comparison, imho.
Didn’t intend it to be condescending, really.
BTW, if any of you guys (or gals) can tell me how to get Excel to pick the last value in a column I would be eternaly grateful. Haven’t found anything in “Help” on this one.
You can go to Edit | Go To, click Special, then select Last Value. But I’m thinking you’re probably looking for a formula to use in the spreadsheet itself.
Assuming your list doesn’t have any blanks, something like this might work. If your list is in column A and starts in row 1:
=INDEX($A:$A,COUNTA($A:$A))
Thanks for the pointer towards INDEX… My column does have blanks in it, but while searching on INDEX, I saw a reference to the CHOOSE command, and that does work perfectly. Thanks a lot - you pointed me towards a solution to a problem that has been bugging me for months!
I found this on the net and it is not affected by blanks:
=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))
How did you do this with the choose function?
The CHOOSE function works by selecting the cells in a column, and then telling it what cell (from the top down) to return. As part of the spreadsheet in question already calculates how many of the cells have values (again from the top down), the Choose function worked OK.
The latest information you presented on the INDEX function may be a little easier - I’ll check it out shortly to see how it works.
Interesting - Ive farted around with this for months and now have two methods of doing it.
Excel has lots of help. But sometimes digging what you need out of the tons of information is a challenge.
Thanks again for the help.
I’ve never used the choose function before so if you have something that works please post the actual formula, even if it’s a harder way of doing it. I didn’t understand the Help version of it.
Every time I see stuff like this I like to play around with it and then save examples for future reference. It saves a lot of time trying to reinvent the wheel when I need a quick formula. I hate spending hours recreating something I already figured out before. I also farted around trying to find the last entry in a column and it involved all kinds of convoluted formulas and hidden columns. I’m finding the internet to be a great place to find Excel formulas if I can ask the right question.
Help is useful if you know exactly what you are looking for, but don’t quite remember how to do it.
The best way to learn Excel is to go through the menu item by item and see what each command does. And then do that for each of the functions.
Here is the formula as I set it up.
=CHOOSE(S29,R5,R6,R7,R8,R9,R10,R11,R12,R13,R14,R15,R16,R17,R18,R18,R20,R21,R22,R23,R24,R25,R26,R27,R28)
S29 is the “pointer”, and R5 thru R28 are the cells that contain data, always entered sequentially from the top down.
The column in question runs from R5 down to R28. Another part of the spreadsheet calculates how many of the cells contain data (always from the top down, with no gaps), and stores that in S29.
Let’s assume that the top five cells contain data. Then cell S29 contains the number 5, and Excel goes to the fifth cell from the top and returns the contents. That gives me the last data in the column, which was what I was after.
This may be cheating a bit as the number of data-containing cells was known (in S29), but it was perfect for what I wanted. I’m going to play around with INDEX to see if it will do it more easily, but haven’t yet.
Some more explanation of the purpose of this - to put things in context. We have a spreadsheet that has data on gross income, for each month of the year. In each sheet he R column, from cell 5 down thru 28, contains our average daily gross income to date, with the last cell containing the latest average daily income. I take that data, multiply it by the working days in the year, and get a projection of annual gross income based on the this latest data. Frankly, right now it’s pretty scary!
Thanks, the choose function makes sense now.