115

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

alt text

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.

Community
  • 1
Jonik
  • 3,838
  • 7
  • 31
  • 43

2 Answers2

124

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.

Community
  • 1
user68665
  • 1,256
  • 1
  • 9
  • 2
35

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".)

ale
  • 52,972
  • 42
  • 165
  • 314