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.

Improve this question
0

2 Answers 2

Reset to default
125

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.

Improve this answer
4
  • 3
    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.
    – Jonik
    May 14, 2014 at 14:02
  • 7
    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.
    – MaryC.fromNZ
    May 15, 2014 at 10:42
  • Ah, yes. I tried with a new spreadsheet and there's indeed "Paste transpose" option!
    – Jonik
    Jun 29, 2014 at 8:12
  • Thank you. Edit > Paste special > Paste transposed (last option).
    – Bishwas Mishra
    Jul 9, 2019 at 4:41
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".)

Improve this answer
3
  • 2
    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.
    – kevinmicke
    Oct 30, 2014 at 16:09
  • 1
    @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
  • 1
    This is also not going to retain the formulas.
    – poolie
    Nov 29, 2015 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.