Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 19 down vote accepted

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.

share|improve this answer
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 '14 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. – MaryC.fromNZ May 15 '14 at 10:42
Ah, yes. I tried with a new spreadsheet and there's indeed "Paste transpose" option! – Jonik Jun 29 '14 at 8:12

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

share|improve this answer
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 '14 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. – Al E. Oct 30 '14 at 20:07
This is also not going to retain the formulas. – poolie Nov 29 '15 at 16:59

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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