I have a spreadsheet in Google Docs with two data rows and lots of columns like so:

Is it possible to easily convert these rows into two columns and lots of rows (obviously retaining all information and formulas)? In other words, to something like this:

`````` 19.9.2009   967,15 €
28.9.2009   950,78 €
12.11.2009   942,03 €
..           ..
``````

There's probably a neat mathematical term for such a conversion, but I don't remember which (matrix something?). Feel free to suggest a better name for the question.

Edit: To clarify, ultimately I want to get rid of the original (two row) version and continue working on the converted one. TRANSPOSE(), mentioned by Al Everett, got me very close but not quite there. I want the raw data in the transposed version, not cells that mirror the original ones.

If you're using the "new" Google Spreadsheets, in the "Edit" tab menu, under "Paste special", the last menu voice is "Paste transposed": it does exactly what you need in a single task.

• Hmm. But there doesn't seem to be "Paste transposed" option in "Paste special" menu. Are you perhaps talking about Excel? This question is about Google Spreadsheets. May 14, 2014 at 14:02
• It's there in new sheets, was not available in the previous version, so will only be in older ones if you have converted them. May 15, 2014 at 10:42
• Ah, yes. I tried with a new spreadsheet and there's indeed "Paste transpose" option! Jun 29, 2014 at 8:12
• Thank you. `Edit` > `Paste special` > `Paste transposed` (last option). Jul 9, 2019 at 4:41

If you're using the old Google Spreadsheets, you want to use the TRANSPOSE() function. (Google help: Array functions)

In the upper left cell where you want this to appear, enter `=TRANSPOSE(A5:Z6)` (or whatever your data range happens to be).

If you want the raw data back, you can copy and paste the values into a fresh set of cells. (Highlight the cells you want to copy. Choose "Edit | Copy". Put your cursor on a new, empty cell. Choose "Edit | Paste values only".)

• While this was definitely the best answer when it was written, the one from @user68665 let's you skip a few steps with the new Google Spreadsheets. Oct 30, 2014 at 16:09
• @kevin: True enough, and I'll edit it to reflect it's for the old Google Spreadsheets. But only the Asker can change the "accept" mark.
– ale
Oct 30, 2014 at 20:07
• This is also not going to retain the formulas. Nov 29, 2015 at 16:59