[vks64]

Can anyone help
1) how to transpose the data in rows into columns and columns into rows in a Excel spread sheet?
2) how to change the numerical order of data in rows/ columns in a Excel spread sheet? i.e.. from the first row to last row and last row to first row ?
Thank "U'

[JBeaucaire]

1) To convert data in a Column into data in a row, highlight the data in the column, click on COPY (or CUT), the click on the target cell where you want the first cell to be, then click on EDIT > PASTE SPECIAL > [ ] Transpose

2a) First, I would use the TRANSPOSE trick above to temporarily but my column data into rows, then I would use my trick below (2b) to reorder them, then transpose them back to columns.

2b) If I want to invert the order of data in rows, I would add an index column and number them from 1 to "whatever" starting at the BOTTOM. Then select the whole set of data including the new index I've added and SORT by the index.

[kurtwz]

Those would work. I might add one last option, which is to use an array formula to refer to another range of cells for which you want to change directions. This is tricky, but basically here's how you do it:

1) Starting with whichever cell you'd like the results to go in (if data are going DOWN column A and you want them to go ACROSS row 1, start in cell B2, for example). Input "=transpose()" without the quotation marks and with the range of cells between the parentheses.

2) Hit enter. Now you should see a value error.

3) Drag select the range of cells over which you'd like the data to be shown (B1:whatever1) starting with the cell that has the first transpose formula in it.

4) Hit ctrl + shift + enter. It should work by now.

If (when!) you have additional questions/problems, you may want to reference the help file by searching for keyword "transpose" or "array". Microsoft makes useful info available for this topic.

[JBeaucaire]

Yes arrays are interesting and useful in small doses like this may be. But arrays are very volatile and affect sheet performance when applied to any meaningfully large dataset... and they easily become unusable at all on large ones.

When a non-array option exists... or you can create one (which is possible all the time)... I recommend using it.

My 3 cents... on a thread 3 months old... one presumes the OP has resolved this and moved on.