How To Pivot & Unpivot Tables In Google Sheets
Вставка
- Опубліковано 26 лип 2024
- 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 ↓↓↓ - Навчання та стиль
Oh how life has changed since FLATTEN was discovered! If only you knew the lore behind the magic!
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.
Awesome work!
Super useful, thanks!
You're absolutely welcome, Mario!
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...
Glad it helped! There's a new couple of functions called TOCOL and TOROW that can be used to 'unpivot'. I might make a video about it one day
Great sir 👍🏻👍🏻👍🏻👍🏻
Thank you!
this is HOT STUFF!!
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
Absolutely. In my video at 2:18, you could change the E4:P7 to E4:7. This will select the entire rows even after more data is added.
Great f formula
Query has pivot built in. Why not use that?
Check out 5:45