How To Pivot & Unpivot Tables In Google Sheets

Before the discovery of a hidden function a couple years ago, unpivotting data was a pain in the neck! Let's see how things have improved thanks to a quick mixture of FLATTEN and SPLIT.

----------------------------------------------------------------------------------------------------
⏳ TIMESTAMPS
----------------------------------------------------------------------------------------------------
0:00 Unpivotting data
3:45 Pivot your data with QUERY
6:05 Using Pivot Tables

----------------------------------------------------------------------------------------------------

💬 GOT A QUESTION?
↓↓↓ Leave a comment down below ↓↓↓

[HashAliNZ]

Oh how life has changed since FLATTEN was discovered! If only you knew the lore behind the magic!

[cric]

So nice, I made a custom function with that stuff ! It allows to select the whole range and set the number of columns to keep on the left side :-). =UNPIVOT(DATA, NbColumnFixed). The formula is rather complex using by BYROW, OFFESET, COLUMNS, ROWS... But works fine. It keeps the headers and just add 1 header for the column name and 1 for the values.

[heikkimoisander]

Thank you! I was actually looking for example to unpivot the data with google query, but you showed how to do it with flatten. Still looking for example for unpivot...

[igcetra]

Thanks for this video, I am trying to adapt this to one of my projects - a personal finance budget/expense tracker - where i set it up as a table and i need to UNPIVOT the data so that i can *then* use that to create some dashboards.

If I expect that data will continue to be inserted in the initial table horizontally (ie. in your case around the 2:18 mark for the purple and blue boxes) how can i automate the UNPIVOT function such that it will capture that data to account for this change? Because i understand you can set up the =ArrayFormula(...) with an exact reference but how do you account for future data with hundreds of entries? Side note: i see how that can be helpful with the =QUERY function when you take into account all the data in the column but not anything that is blank at 4:29

[spauldinggg]

Query has pivot built in. Why not use that?