How to turn many rows and columns of data to line up in one column in Excel?

Question

So I have data that's 1000 rows and 10 columns in Excel. However, I have to line it up into one column in order to put it into SPSS. How can I do it without tediously moving columns?

Answer 1

highligting 10 columns is far from tedious. Go to the top row of the frist column you want to move. Hold down the shift key, press down, then the end key. Right click and select copy. Poof all 1000 rows are copied. Move the the first row of the column you want to make longer. press down and the end key, poof you are at the bottom. Rightclick and press paste. Repeat 8 more times. You can do it all in less time than it took to write your question.

Answer 2

You can rig a formula from the OFFSET() or INDIRECT() functions, along with ROW() and some math to feed the reference values. It may help to use a different sheet.



Assuming your data starts in cell A2 on Sheet1, put this formula in Sheet2 A1:

=OFFSET(Sheet1!$A$2, INT((ROW() - 1)/10), MOD(ROW() - 1,10))



It might take some time to get the formula just right, but once you find a suitable one, you can quickly enter it in any cell range you want by typing the range reference (something like A1:A10000) in the Name Box, typing the formula, and pressing Ctrl+Enter. Or, you can enter the formula, select the range you want to fill, and press Ctrl+D to duplicate the formula from the active cell to all other cells.

Answer 3

There is a transpose function. This is different form the paste transpose feature