I have spent about a day in search of how to unpivot data, found several videos and formulas all way too complicated, this video puts it simple, clear and straight to the point. Thank you David for this, is the best video I found on unpivoting data.
A lot of my work involves unpivoting wide tables. A few years ago I was using a Google Apps script I found (creatively named _UNPIVOT)_ which worked well enough but had limited flexibility and would occasionally bug out and fail to load. About 2 years ago someone on the Google product forums showed me an undocumented function called _FLATTEN_ and I was instantly hooked. A little while later _FLATTEN_ was canonized and I no longer had a secret weapon. 😂 Anyway, speaking as someone who's been using it for a long time, I can say that this is the best tutorial of the function that I've seen so far, and I wish it existed three years ago.
Awwww that’s so sweet!! Thanks so much. I also used it once or twice when it was a secret weapon as I love using unpivot in power query. Spread the love! Great comment to read 😃
was wondering if there was any way that you could bypass those columns you had to delete on the column pairs section. Im trying to automate some sheets and having to delete those non useful ones causes me issues. this has been so helpful!
Glad you find it helpful, the process in this video is the most efficient way I have found, but you can create a named function to do it with less code. If you have Excel data though, then you can also use Power Query to get to a no code solution.
Great Video and explaining , i knew this methode but i wanted to exclude empty rows with no data ,now i know it with query , perfect .... just one Problem what to do with empty Celles ?
Hi the empty rows would be excluded with query but the empty cells would be included as separators which is usually what is required otherwise the columns don’t stack up properly. If you don’t enter the last argument in the SPLIT formula you can do different things with empty cells though.
Thanks a lot for the video! Unfortunately I have a problem, I have exactly the same data and cells as the sheet on the video and yet when I write the formula I have a blank cell... no error or formatting problem
Hi Oni. Sorry to hear, unfortunately its hard to understand what is going on from a short text explanation. If you ask in one of the G Sheets forums you can send pictures and files alongside posts which may be more help than I can give - apologies
Hi, very interesting but didn´t help me. I am looking to unpivot but to make as much lines as the value (in numbers) appears in the data. Example: in your first table, 736 lines for "california Jack Jan", 21 lines for California Jack Feb", and so on. Is that possible?
This has been extremely helpful. I have been trying to figure out how to Unpivot data from the work assignment calendar that my boss puts out every month in a poorly formatted table so I can do things like import the data into google calendar. Ive mostly got it figured out but sometimes he puts more than one name in a cell, and I can't figure out how to separate them into new rows instead of columns. So here's my function ... =QUERY(ARRAYFORMULA(SPLIT(FLATTEN(B3:AE22&"|"&B2:AE2&"|"&A3:A22),"|",TRUE,FALSE)),"Select * where Col1 is not null order by Col1 asc, Col2 asc") Any ideas how i can split cells that contain more than one entry to create more rows instead of more columns? for example I want BL;CB 44889 PTO to appear as BL 44889 PTO CB 44889 PTO Also that number '44889' is a date. why is it formatted as a date in my original array, but not in the output?
Hiya thanks for the feedback I’m glad you like it, with the date, excel/sheets store dates as numbers so you would need to use =TEXT(value,”dd-mmm-yy) to take it as a date
I don’t think your other request is possible without having the source data explicitly shown is possible, it would probably need an extra step before the query to repeat the 44889 PTO in both rows
I have spent about a day in search of how to unpivot data, found several videos and formulas all way too complicated, this video puts it simple, clear and straight to the point. Thank you David for this, is the best video I found on unpivoting data.
Wow! That’s so nice to read, really happy that it helped you! Don’t feel shy sharing it around 😃
A lot of my work involves unpivoting wide tables. A few years ago I was using a Google Apps script I found (creatively named _UNPIVOT)_ which worked well enough but had limited flexibility and would occasionally bug out and fail to load. About 2 years ago someone on the Google product forums showed me an undocumented function called _FLATTEN_ and I was instantly hooked. A little while later _FLATTEN_ was canonized and I no longer had a secret weapon. 😂 Anyway, speaking as someone who's been using it for a long time, I can say that this is the best tutorial of the function that I've seen so far, and I wish it existed three years ago.
Awwww that’s so sweet!! Thanks so much. I also used it once or twice when it was a secret weapon as I love using unpivot in power query. Spread the love! Great comment to read 😃
Precisava muito desse conteúdo, ninguém no BR produziu.
Me ajudou muito, irmão. Sucesso pra você! 🚀🔥
Thanks so much! Glad it was helpful!
Thank you so much for this video!!! I knew it had to be possible somehow, spent hours looking for a solution and this worked magically!
Yay! That’s a really lovely comment to read, glad it worked out. Happy new year 😃
You've solved a life-long issue of mine in 5 minutes! thank you :)
That’s amazing! Thanks for the kind words, in excel the equivalent is attainable via power query
That's EXACTLY what I needed for working with multiple datasets that have different pivoting.
Yay glad you like it!
was wondering if there was any way that you could bypass those columns you had to delete on the column pairs section. Im trying to automate some sheets and having to delete those non useful ones causes me issues. this has been so helpful!
Glad you find it helpful, the process in this video is the most efficient way I have found, but you can create a named function to do it with less code. If you have Excel data though, then you can also use Power Query to get to a no code solution.
Great Video and explaining , i knew this methode but i wanted to exclude empty rows with no data ,now i know it with query , perfect .... just one Problem what to do with empty Celles ?
Hi the empty rows would be excluded with query but the empty cells would be included as separators which is usually what is required otherwise the columns don’t stack up properly. If you don’t enter the last argument in the SPLIT formula you can do different things with empty cells though.
An awesome lesson! Thanks for showing these techniques in Google Sheets. Thumbs up!!
Glad you like it! I had to make this for a client job so I learned how then made a video!
Thanks a lot for the video! Unfortunately I have a problem, I have exactly the same data and cells as the sheet on the video and yet when I write the formula I have a blank cell... no error or formatting problem
Hi Oni. Sorry to hear, unfortunately its hard to understand what is going on from a short text explanation. If you ask in one of the G Sheets forums you can send pictures and files alongside posts which may be more help than I can give - apologies
Perfetc. How Do that Vertical to Horizontal? Opposite that you did.
It its jut one line TOROW should work, or to just flip, TRANSPOSE
Hi, very interesting but didn´t help me. I am looking to unpivot but to make as much lines as the value (in numbers) appears in the data. Example: in your first table, 736 lines for "california Jack Jan", 21 lines for California Jack Feb", and so on. Is that possible?
Hmmm even with Excel’s power query that is very difficult & I don’t know how I would approach it with google sheets
Thank you so much!!! You are a genious!! (Muchas gracias, saludos desde Argentina!!)
Es siempre bien de recibir mensajes como estas. Gracias Marcos!
Great video David but a bit fast for me in places! Is it possible to have access to the file please?
Yes sure! Send me an email to david@xlconsulting-asia.com
This is super useful!!!
Glad you like it!
This has been extremely helpful. I have been trying to figure out how to Unpivot data from the work assignment calendar that my boss puts out every month in a poorly formatted table so I can do things like import the data into google calendar. Ive mostly got it figured out but sometimes he puts more than one name in a cell, and I can't figure out how to separate them into new rows instead of columns. So here's my function ... =QUERY(ARRAYFORMULA(SPLIT(FLATTEN(B3:AE22&"|"&B2:AE2&"|"&A3:A22),"|",TRUE,FALSE)),"Select * where Col1 is not null order by Col1 asc, Col2 asc")
Any ideas how i can split cells that contain more than one entry to create more rows instead of more columns? for example I want
BL;CB 44889 PTO
to appear as
BL 44889 PTO
CB 44889 PTO
Also that number '44889' is a date. why is it formatted as a date in my original array, but not in the output?
Hiya thanks for the feedback I’m glad you like it, with the date, excel/sheets store dates as numbers so you would need to use =TEXT(value,”dd-mmm-yy) to take it as a date
I don’t think your other request is possible without having the source data explicitly shown is possible, it would probably need an extra step before the query to repeat the 44889 PTO in both rows
super awesome
Thx
Yay