Excel Question. How can I change data from rows to columns?

Question

Right now I have numbers going across like:
256, 556, 448, 121 etc
I need them to go:
256
556
448
121

I tried to copy and paste but it didn't work. It said something about changing destination cells. Can someone help?

This is NOT a read only spreadsheet

Answer 1

Aladou is right. Select your first range and copy it. Then select the cell at the top of the range in the column where you would like your data to be put and go to:

Edit > Paste Special...

Then at the bottom make sure that the checkbox for Transpose is checked and click OK.

Make sure not to select more than one cell when you go to paste the cells. If for example you select 10 cells to copy, then select 2 cells and try pasting them in those two cells, it thinks you are trying to fit 10 cells into 2 cells and won't allow it.

Also, if you are trying to paste over merged cells. That can give you problems. So don't attempt pasting cells over an area that has merged cells.

If the cells contain formulas or if formulas in other cells reference those cells, make sure to double check the formulas afterwards. They may have changed and that could throw off your formula answers if not careful.

That's all you have to do to transpose them. You don't need a macro for something like that.

When transpose is checked, Excel will rotate how it places the cells by 90 degrees. So if they are horizontal it will place them vertical and vice versa.

Answer 2

You would think this is an easy task. Not so. It would require a macro to make it happen.
If you don't have too many cells to change you can type in formulas in a column as follows:
If the row is A1 and you want numbers in col A then type:
in A2.... =a1
in A3....=b1
in A3....=c1
etc.
When you are done you can move the col to a new location if you want.

The macro to accomplish this is fairly complex. It involves a for next loop to move everything.
Email me if you need the macro and I will see if I can find it.

Answer 3

you shouldn't have any trouble moving the data from one cell to another. Are you working with a read only document? That could be the problem.

Answer 4

- Edit-Copy the cells going across
- go to where you want to paste them (can't be where the numbers are now -- try one row below, for example)
- Edit - Paste Special - Check Transpose at the bottom

Let us know if this still doesn't help.
Good luck.
.