# Transpose multiple columns into single column

I have a Google sheet in which the rows are unique cities and the columns are years. It can grow in two dimensions.

|            |         2010         |          2011        |
-----------------------------------------------------------
|    City    | Temp low | Temp high | Temp low | Temp high |
-----------------------------------------------------------
| London     |    10    |     11    |    21    |     22    |
| Birmingham |    12    |     13    |    23    |     24    |

However, this double header doesn't work for creating a pivot table.

How can I transform the table such that it grows only in the row dimension and the years are simply a value in a single column?

|    City    | Year | Temp low | Temp high |
-------------------------------------------
| London     | 2010 |    10    |     11    |
| Birmingham | 2010 |    12    |     13    |
| London     | 2011 |    21    |     22    |
| Birmingham | 2011 |    23    |     24    |

Here is a sample sheet of the above example.

The formula shouldn't need to be adapted when new rows or columns are added. However, the column pattern will remain the same and can be relied on.

=QUERY({{"Year";ArrayFormula(TRANSPOSE(SPLIT(JOIN(" ",REPT(B1:E1&" ",COUNTA(A3:A)))," ")))},
query({A2:A,B2:C;A3:A,D3:E},
"where Col1 is not null",1)},
"select Col2, Col1, Col3, Col4 order by Col1, Col2",1)

If you want to order by city and then by year use

=QUERY({{"Year";ArrayFormula(TRANSPOSE(SPLIT(JOIN(" ",REPT(B1:E1&" ",COUNTA(A3:A)))," ")))},
query({A2:A,B2:C;A3:A,D3:E},
"where Col1 is not null",1)},
"select Col2, Col1, Col3, Col4 order by Col2, Col1",1)

(The above formula is actually a modified version of this answer. Please refer to it for more info and how the formula works.)

• Thanks! Is there a possibility to make this formula adaptive? Currently, it needs to be extended manually if there are new year columns added. Imagine I have 20 more columns to the right...
– mdcq
Commented Jan 15, 2021 at 0:04
• Sure. You just add groups like A3:A,F3:G;A3:A,H3:I;A3:A,J3:K and so on. You also need to adjust your REPT values to B1:K1. I could show you at your test sheet but it is view only. Commented Jan 15, 2021 at 0:15
• "Thanks!" Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. Commented Jan 15, 2021 at 0:16
• Sure, but that doesn't scale and is error prone. I need a formula that doesn't need changing when I add new columns.
– mdcq
Commented Jan 15, 2021 at 0:18
• Fair enough. Glad I could help :) Commented Jan 15, 2021 at 0:20

So, turns out, the general problem is called "Unpivoting". Unfortunately there is no function in Google Sheets yet.

I found the following answers useful.

## Manual

How do you create a “reverse pivot” in Google Sheets?

## Using Script

How do you create a “reverse pivot” in Google Sheets?

## Other

Transpose Ranges repeated by row

Converting multiple columns to list

Analyse multiple columns per row