How To Pivot & Unpivot Tables In Google Sheets

[@HashAliNZ]

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

[@FireplaceAquarium]

By far the best and easiest explanation of how to unpivot data. Brilliant!

[@ChristophSontheimer]

Incredible: short, concise, and very helpful - thanks for this great tutorial!

[@CyberGalVideos]

GENIUS!!! Please don't judge me for being so excited about a g-sheet. I don't think I have been this happy in months

[@HashAliNZ]

Don't worry about it, I nerd out all the time about cool Sheets things

[@cric8482]

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.

[@HashAliNZ]

Awesome work!

[@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...

[@HashAliNZ]

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

[@MarioHernandez-dt7ji]

Super useful, thanks!

[@HashAliNZ]

You're absolutely welcome, Mario!

[@krishnatate4765]

Great sir 👍🏻👍🏻👍🏻👍🏻

[@HashAliNZ]

Thank you!

[@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

[@HashAliNZ]

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.

[@edwinandrescortesmunera855]

this is HOT STUFF!!

[@abdoulayeleye5399]

Nice nice nice🎉

[@spauldinggg]

Query has pivot built in. Why not use that?

[@HashAliNZ]

Check out 5:45

[@JSBernate]

Great f formula