Leila Excelent video as always giving to us the xtra step on the analysis of each new function and not just showing the basics! with your video you took a deep analysis through these cool and new excel functions!! as always my hat off for you!!
Dont take this the wrong way but zzzzzz..... You've got a masters in economics, maybe do a vid about economics? I get it tho, youre shit hot at excel, you passionate about anything else? 😃
You have to admit that the yearly fee for 365 is worth it for all of the work that team is doing putting in these cool functions. The Lambda inside blew my mind.
@@dougmphilly I've found some pretty good uses for Lambda in the name manager. Just this week. I wanted to calculate some stats based on three inputs for 4 columns of data. I used hstack to spill the results and instead of having the formula for computing the stats repeated 4 times in the hstack I created a 'r_stat' lambda and passed it the values. Assume x,y,z are the inputs. HSTACK(r_stat(x,y,z,Choosecols(myarray,1)),r_stat(x,y,z,Choosecols(myarray,2)), etc...
Your teaching style is enlightening. So many people make tutorials and they have zero skills on how to break things down accurately and easy to follow. You do a great job. I go to your channel everytime I need to know how to do something.
Very cool functions, Leila! For most Excel users working in an office setting, though, I still think pivot tables are easier to use. Most people won't be writing lambda functions, and while I think these formulas are awesome, they could be confusing to many people versus pivot tables. Great demo of these functions, though! Thanks for sharing !!
Oh this is wonderful! Thank you to the Microsoft team for coming up with useful new features and thank you to Leila for giving us such thorough demonstration. I really appreciate that you go into each available arguments of a function and give us different scenarios that we could be dealing with on a daily basis. ❤
Friday morning? ... almost slipped by unnoticed ... thankfully my coffee was close by. Once again, new features that I would not have known about without Prof Leila ... an effortless presentation ... and 'seeds' have been planted. Thank you ... thank you ... thank you ... 😍😍😍
Love this! Dynamic grouping and pivoting without having to refresh is awesome. Using TEXT is a big plus that's been seriously needed for a long time! I only wish it came with formatting options. Only options to format nicely when number of rows or columns change is to either manually change formatting every time or work with conditional formatting formulas to get them just right. Either way, a little extra work to make a sheet presentable
Wow!!! This is awesome to know that the almighty report 'curator' in Excel (Pivot Table) can be overthrown😅 Thank you @LeilaGharani for always bringing to us the most updated happening in the world of Excel. Looking forward to the release in Office 365.
These new (to me) functions work just like magic, learned a ton in just 15 mins! Thanks so much! (Not to mention having Jesse Pinkman and Walter White in the 'Health' Division really is spot on 😅)
Thank you Leila for such a cool video that makes me so impatient to have those formulas in my version of Excel! Your videos allow me to continue upping my Excel game ❤️
Very cool functions, Leila! For most Excel users working in an office setting, though, I still think pivot tables are easier to use. Most people won't be writing lambda functions, and while I think these formulas are awesome, they could be confusing to many people versus pivot tables. Great demo of these functions, though! Thanks for sharing !!
This is incredible. Seemingly Power Bi like concepts and functions are slowly being migrated to Excel. Maybe one day all BI tools will all be built in Excel.
This is awesome and thank you for sharing so many examples, very comprehensive. Just from looking at the GROUPBY function in this video I think it will invalidate the need to use UNIQUE, SORT, FILTER, SUMIFS and COUNTIFS in many cases. Amazing!
Awesome. You are the best Leila! Were you looking over my shoulder as I was thinking today! Power Query is awesome but I like No 1 best. I use all, No 2 is useful for IF, OR and IFS . 🎉
Thank you deeply for all what you are doing , it is so great, i just starting using the groupby function and it works fine except that the sum function is not working, it always give me Name error, but all other function works, I over ride this by using lambda(a,sum(a)) instead and it worked💗
Very cool features. But you could probably construct a conventional pivot table more quickly via the traditional method. Also, can you generate a compact design in which 2 fields populate the rows area, one beneath the other?
Yeah there isn't anything useful that you can't do with a pivot table. Personally I like using power query to group by because the output is in the form of an Excel table and you can add formulas that will dynamically be expanded to any new rows when you refresh with a new data set.
My thinking is that first you draft by building the pivot, paly with the structure more easily and once you got the final structure, implement that with groupby/pivotby so it updates dynamically and you don't need to refresh.
I've been waiting for these functions to filter down to my beta for months, so very happy to see they have now arrived. Having to remember to refresh pivot tables drives me nuts, so it looks like a lot of work coming up to (try to?) replace my many pivot tables with the new functions. Some are rather complex though. And quite a few need PQuery to clean the data first, and it would likely be too complex to try to eg use filter(). Great video!
Awesome 🎉 At the moment im struggling with one task, maybe you can support me how to do. Task: Which two items are the most frequently used item. Consider the transaction code. Table 1: Column A: transaction Column B: item Column C: quantity Table 2: Column A: all possible combination of the items Column B: (result) how often the combination has been used.
Wow, another amazing video Leila, I watch all your videos and wish to be as skilled as you one day! You are opening my doors to my dream job in Data Analytics!
@leila gharani, I wanted to try these functions but I dont have them? I have updated my microsoft account, and yes, I have 365. What else could I try? Please help.
Thank you, Leila. Very helpful. My use case has more to show, namely labor hours, cost, sell and profit totals. I only want to sort by 1 column, but show these multiple columns? You mentioned near the beginning about just selecting more columns, but it doesn't seem to be that simple as I get errors when I try. The Help on the function by Microsoft also does not show an example, so I am looking for more in-depth advice.
I appreciate your efforts in enhancing user capabilities through updates. Please provide guidance on utilizing the GROUPBY & PIVOTBY functions for multiple tables across several sheets.
Again an exceptionally good explenation. You get to the heart of the matter clearly and cincisely. Tank you for your inspiration using these techniques in my daily excel work.
Learning quite a bit of NEW sheet rules, kind of easy and complicated both at the same time, alien functions to me, but, very, very useful functions rules to follow! 😺🥰⭐️💎
OMG, you're so smart and helpful. THANK YOU Leila! I love how you give real life examples, such as working with a spreadsheet someone else made and where they didn't quite follow good spreadsheet husbandry. :)
These formulas will save me a lot of time and energy. Thanks Leila! I've also learnt you have Walter White and Jesse Pinkman as Sales Managers in the HEALTH Division... I like your humor.
Thank you Leila, I was missing your video and thought that maybe you are busy with Global Excel Summit. Excel is really becoming more and more powerful and I look forward to you to be my mentor continually in this exciting journey! 😊👍
Thanks, Vijay! I did slow down on UA-cam because we want to focus more on courses. But we'll continue to post if we come across some exciting stuff. Thanks for being part of our journey!
Absolutely fantastic video! Your clear explanations made complex concepts feel so approachable. Looking forward to more insightful content from your channel!
I have been following all of your excel tips so far and it helped me a lot. Now I had to change my OS and started using macOS. Please help with your insights with using office excel in Mac.
Do you know of a way to filter the summed results I am getting using group by? For example, I would only want to see results if the sum of an item was over 100.
Thanks Leila for upgrading us before they are shown off. Currently these are in Beta version, any guess by which month of this year these will be made available in office 365
@@LeilaGharaniHi Leila, I switched to the beta channel and I got updated to Excel version 16.82 (24011715) but there is neither groupby, nor pivotby is available. Which Excel version do you have, where these functions are available. LG, Chris
Love these, thanks a lot. Might be able to replace my existing arrays where I have used Unique for category, Transpose(Unique) for months and finally SUMIFS for the Sum
Once again, formulas that I do not have because they are only available to insiders, It would be nice if you would mention this at the satart s I dont waste time watching instructions for features I don't have. You are well respected and followed please consider advising us when these functions are only available to insiders
There's not much british female asian muslim/non muslim middle eastern I.T specialist videos here on youtube, great video btw Leila, brand new british subscriber here
Great Video!! These are really game changers... Now I'm starting to think how could I replace all my pivot tables, so I don't need to refresh manually! And, by the way... I also loved the shirt!!!
a very well put together video - While i think the functions look cool - a pivot table, is a much better solution in most cases - even more so if you combine it with Power Query and connection only (which works for non-Power Pivot pivotables) This saves space and file size - agreed it's good for some charts you might want to create and for a cleaner look
Thank you Leila, you always share amazing functions. Just wondering, if I have 10-20 PV tables in the worksheet, is that better to use these functions instead? Compare with pivot table, which one will slow down Excel? Thank you
Great video. Wondering how you would apply formatting to the result to make reading easier, problem is it is dynamic so formatting should also be dynamic?
Hi Leila. You might want to send out a subscriber note that GroupBy crashes Excel. With me, at least a dozen times when attaching Slicers to filter the GroupBy results. This problem has been reported to MS but you might want to lend your voice to getting the bug fixed. Thanks for all you do.
awesome video and functions! How would you show quarters using these functions (so something link 2023 - Q1 etc) without needing to add a Quarter column to the data?
These functions are realy amazing since I am starting to move away from Pivots due to their static nature. I have a couple of doubts however: do you need to apply these formulas to a range set as a Table and is it possible to return multiple values (sum and avergae for example)? it would also be interesting to know if these functions are faster and/or "heavier" than Pivots
Grab the file I used in the video from here 👉 pages.xelplus.com/groupby-pivotby-function
These functions are available in Excel for Microsoft 365.
Is there any estimate as to when they will be available?
Leila Excelent video as always giving to us the xtra step on the analysis of each new function and not just showing the basics! with your video you took a deep analysis through these cool and new excel functions!! as always my hat off for you!!
@@UtuDudas That's my question as well
Dont take this the wrong way but zzzzzz.....
You've got a masters in economics, maybe do a vid about economics?
I get it tho, youre shit hot at excel, you passionate about anything else? 😃
i hope it will be soon
These are game changers in my opinion. The dynamic behaviour, especially for PivotBy is so useful.
👍
I agree completely
When will it be native in Excel???@@LeilaGharani
I can’t believe I never saw your videos before. The clarity you display is unparalleled.
Thank you for these tips!
Glad you like them!
welcome to the fan club. She's the greatest. Been following for years.
Very nice use of the HSTACK function to create a contiguous set of row fields. Well done.
You have to admit that the yearly fee for 365 is worth it for all of the work that team is doing putting in these cool functions. The Lambda inside blew my mind.
The amount of new features and functions in the last couple of years is amazing. They're really doing a great job.
probably the first practical use for lambda
@@dougmphilly I've found some pretty good uses for Lambda in the name manager. Just this week. I wanted to calculate some stats based on three inputs for 4 columns of data. I used hstack to spill the results and instead of having the formula for computing the stats repeated 4 times in the hstack I created a 'r_stat' lambda and passed it the values. Assume x,y,z are the inputs. HSTACK(r_stat(x,y,z,Choosecols(myarray,1)),r_stat(x,y,z,Choosecols(myarray,2)), etc...
@@olliehopnoodle4628 way above my paygrade. its the one function i struggle with.
All things that Access did for years...
Your teaching style is enlightening. So many people make tutorials and they have zero skills on how to break things down accurately and easy to follow. You do a great job. I go to your channel everytime I need to know how to do something.
Very cool functions, Leila! For most Excel users working in an office setting, though, I still think pivot tables are easier to use. Most people won't be writing lambda functions, and while I think these formulas are awesome, they could be confusing to many people versus pivot tables. Great demo of these functions, though! Thanks for sharing !!
Oh this is wonderful! Thank you to the Microsoft team for coming up with useful new features and thank you to Leila for giving us such thorough demonstration. I really appreciate that you go into each available arguments of a function and give us different scenarios that we could be dealing with on a daily basis. ❤
Friday morning? ... almost slipped by unnoticed ... thankfully my coffee was close by.
Once again, new features that I would not have known about without Prof Leila ... an effortless presentation ... and 'seeds' have been planted.
Thank you ... thank you ... thank you ... 😍😍😍
Thanks for tuning in even on Friday! I hope some cake was also in the mix. 😊
@@LeilaGharaniI need your back, please
Excellent delivery of knowledge..thanks a lot
Love this! Dynamic grouping and pivoting without having to refresh is awesome. Using TEXT is a big plus that's been seriously needed for a long time! I only wish it came with formatting options. Only options to format nicely when number of rows or columns change is to either manually change formatting every time or work with conditional formatting formulas to get them just right. Either way, a little extra work to make a sheet presentable
Glad you like it!
Leila, you always explain things so well! Thank you!
Wow!!! This is awesome to know that the almighty report 'curator' in Excel (Pivot Table) can be overthrown😅
Thank you @LeilaGharani for always bringing to us the most updated happening in the world of Excel. Looking forward to the release in Office 365.
Thanks Leila for covering these amazing functions in depth.
I loved the table with Walter White, Jesse Pinkman, Christopher Moltisanti, etc. You are the best
Teacher Leila, Your Teaching and your Materials as beautiful as your name is. Thank you for sharing.
This is probably the best new function I’ve seen. The multitude of uses without even thinking about it is staggering.
Agreed! It's really a great addition.
@@LeilaGharanineed your back
Your explanations are always the easiest to understand. Thank you.
Thank you Leila for all you do. I've learnt a lot from you. Keep it up!
These new (to me) functions work just like magic, learned a ton in just 15 mins! Thanks so much!
(Not to mention having Jesse Pinkman and Walter White in the 'Health' Division really is spot on 😅)
Thank you Leila for such a cool video that makes me so impatient to have those formulas in my version of Excel! Your videos allow me to continue upping my Excel game ❤️
Glad the videos are helpful for you! Let's hope the functions will be rolled out soon.
Mam I'm grateful to you ... New function clubbed with other available function and your teaching way is awesome ... God Bless You 🙏
Very cool functions, Leila! For most Excel users working in an office setting, though, I still think pivot tables are easier to use. Most people won't be writing lambda functions, and while I think these formulas are awesome, they could be confusing to many people versus pivot tables. Great demo of these functions, though! Thanks for sharing !!
As always Leila, you are great at explaining new features. Thank you.
Glad you enjoyed it. Thanks for watching!
This is incredible. Seemingly Power Bi like concepts and functions are slowly being migrated to Excel. Maybe one day all BI tools will all be built in Excel.
The lambda (λ) function is a really powerful addition to this very useful function. Many thanks for this excellent upload.
I used macro to refresh my pivot tables. With Groupby and Pivotby, no need. Amazing function. Thanks, Leila!
You're welcome!
This is awesome and thank you for sharing so many examples, very comprehensive. Just from looking at the GROUPBY function in this video I think it will invalidate the need to use UNIQUE, SORT, FILTER, SUMIFS and COUNTIFS in many cases. Amazing!
Thank you for sharing this, Leila !! This gonna make synthesizing data so much faster and cleaner in a lot of cases. I love the dynamic aspect of it.
Awesome. You are the best Leila! Were you looking over my shoulder as I was thinking today! Power Query is awesome but I like No 1 best. I use all, No 2 is useful for IF, OR and IFS . 🎉
I am really blown away by how massively useful this new function is.
Absolute game-changers for excel freaks ! Thank you for the crisp and clear teaching!
Glad it was helpful!
Thank you deeply for all what you are doing , it is so great, i just starting using the groupby function and it works fine except that the sum function is not working, it always give me Name error, but all other function works, I over ride this by using lambda(a,sum(a)) instead and it worked💗
This is a powerful addition, nice to see Excel continue evolving
Very cool features. But you could probably construct a conventional pivot table more quickly via the traditional method. Also, can you generate a compact design in which 2 fields populate the rows area, one beneath the other?
Yeah there isn't anything useful that you can't do with a pivot table. Personally I like using power query to group by because the output is in the form of an Excel table and you can add formulas that will dynamically be expanded to any new rows when you refresh with a new data set.
My thinking is that first you draft by building the pivot, paly with the structure more easily and once you got the final structure, implement that with groupby/pivotby so it updates dynamically and you don't need to refresh.
I've learnt almost your Excel course only from Udemy. Your courses are true gems :D
The FILTER function is the one that I use the most from all the dynamic arrays functions. FILTER is the 🐐
Very high up on my list too. 😊
Wowsers!! Thanks for the great tutorial!!! Love it!!! I needed to know this yesterday literally! Happy to know it now!🙌🏾
Glad you liked it. Thanks for watching!
Excel is the most interesting thing that I found !! This Application is Awesome ❤❤❤
Finally, I have been dreaming of this. I used to use Power query do to do this.
you are the best Leila, best videos on you tube
I've been waiting for these functions to filter down to my beta for months, so very happy to see they have now arrived. Having to remember to refresh pivot tables drives me nuts, so it looks like a lot of work coming up to (try to?) replace my many pivot tables with the new functions. Some are rather complex though.
And quite a few need PQuery to clean the data first, and it would likely be too complex to try to eg use filter().
Great video!
Awesome 🎉
At the moment im struggling with one task, maybe you can support me how to do.
Task:
Which two items are the most frequently used item. Consider the transaction code.
Table 1:
Column A: transaction
Column B: item
Column C: quantity
Table 2:
Column A: all possible combination of the items
Column B: (result) how often the combination has been used.
Wow, another amazing video Leila, I watch all your videos and wish to be as skilled as you one day! You are opening my doors to my dream job in Data Analytics!
@leila gharani, I wanted to try these functions but I dont have them? I have updated my microsoft account, and yes, I have 365. What else could I try? Please help.
Thank you, Leila. Very helpful. My use case has more to show, namely labor hours, cost, sell and profit totals. I only want to sort by 1 column, but show these multiple columns? You mentioned near the beginning about just selecting more columns, but it doesn't seem to be that simple as I get errors when I try. The Help on the function by Microsoft also does not show an example, so I am looking for more in-depth advice.
Fantástic and clear as always.Thanks a lot Leila👏👏👏👏🙏
Wonderful tutorial on amazing new functions. Thank you, Leila!!!
Glad you like it!
I appreciate your efforts in enhancing user capabilities through updates. Please provide guidance on utilizing the GROUPBY & PIVOTBY functions for multiple tables across several sheets.
Again an exceptionally good explenation. You get to the heart of the matter clearly and cincisely. Tank you for your inspiration using these techniques in my daily excel work.
Wow, thank you!
Second video I watched on these two new functions, yours was easier to understand.
Learning quite a bit of NEW sheet rules, kind of easy and complicated both at the same time, alien functions to me, but, very, very useful functions rules to follow! 😺🥰⭐️💎
This is why you're my 'Go To' whenever I get a new challenge. This is so cool and I'm seeing lots of opportunities to play. Thank you
That's so nice! Thank you for watching.
Thank you Leila. I have learned so much from your videos
This is awesome! I can't wait for the GROUPBY to be available. Thanks for letting us know ❤
Our pleasure!
OMG, you're so smart and helpful. THANK YOU Leila! I love how you give real life examples, such as working with a spreadsheet someone else made and where they didn't quite follow good spreadsheet husbandry. :)
My pleasure! There should always be spreadsheet husbandry. 😁
These formulas will save me a lot of time and energy. Thanks Leila!
I've also learnt you have Walter White and Jesse Pinkman as Sales Managers in the HEALTH Division... I like your humor.
😁
Can´t wait to get in touch with it !!!! Thank you, Leila !!!
Thank you Leila, I was missing your video and thought that maybe you are busy with Global Excel Summit. Excel is really becoming more and more powerful and I look forward to you to be my mentor continually in this exciting journey! 😊👍
Thanks, Vijay! I did slow down on UA-cam because we want to focus more on courses. But we'll continue to post if we come across some exciting stuff. Thanks for being part of our journey!
Absolutely fantastic video! Your clear explanations made complex concepts feel so approachable. Looking forward to more insightful content from your channel!
Awesome, thank you!
Thank you Leila, you are the best. These are very helpful formulas.
Explained beautifully as usual, you are the best👏👏 Thank you🙏😌
Thank you so much Leila Sensei. I am amazed by the functionality of these function. I said goodbye to pivot tables and ifs functions.
Our pleasure. Thanks for watching!
Totally needed this!!!! sharing it many of my buddies!!!!
Thanks for sharing it!
Thanks again for this amazing and useful master class! I loved ❤ . Rain of blessing to you.
I have been following all of your excel tips so far and it helped me a lot. Now I had to change my OS and started using macOS. Please help with your insights with using office excel in Mac.
What a gamechanger. Saves so much time these two new features. Too bad this is not supported on Mac (yet)
Very good, structured and easy under stable teaching. Thanks.
Glad you liked it.
I am able to see these functions on Excel now! So excited!!!!
Great to hear!
Do you know of a way to filter the summed results I am getting using group by? For example, I would only want to see results if the sum of an item was over 100.
Thank you very much for a clear, distinctive and useful explanation
My pleasure. Glad you liked it.
Thanks Leila for upgrading us before they are shown off. Currently these are in Beta version, any guess by which month of this year these will be made available in office 365
Unfortunately, Microsoft does not provide fixed ETAs. It will depend on the feedback of the users.
@@LeilaGharaniHi Leila, I switched to the beta channel and I got updated to Excel version 16.82 (24011715) but there is neither groupby, nor pivotby is available. Which Excel version do you have, where these functions are available. LG, Chris
Group by is awesome! Sometimes I work with a combination with SUM, FILTER, INDIRECT and UNIQUE to do the same it can do.
👍
Wow!, You are great! Your shorts shows me how meany time I have overworked....
Better future ahead 😉
Very Useful for me thanks Leila 💓
Mouthwatering functions. can't wait
Love these, thanks a lot. Might be able to replace my existing arrays where I have used Unique for category, Transpose(Unique) for months and finally SUMIFS for the Sum
👍
Amazing!! I loved these options!!!
Very clear and helpful. Thank you so much for the video.
Our pleasure. Thanks for watching!
Brilliant. You are a great teacher! Thank you!
Thank you! 😃
Yes - I need this!!! Super post (as always). Happy New Year!
Glad you like it! Happy New Year to you too!
Once again, formulas that I do not have because they are only available to insiders, It would be nice if you would mention this at the satart s I dont waste time watching instructions for features I don't have. You are well respected and followed please consider advising us when these functions are only available to insiders
happy New Year Leila thank you for all you do 🙂
You had me at live updates ❤
There's not much british female asian muslim/non muslim middle eastern I.T specialist videos here on youtube, great video btw Leila, brand new british subscriber here
Very useful! Look forward using them
Thanks Leila! Looking forward to getting these functions; they're still not in my Excel 365.
I hope it doesn’t take too long for them to roll out.
Great video. I've wanted this functionality for a long time. Thanks so much.
Glad you like it!
Great Video!!
These are really game changers... Now I'm starting to think how could I replace all my pivot tables, so I don't need to refresh manually!
And, by the way... I also loved the shirt!!!
Thank you! T-shirt is our own design :)
a very well put together video -
While i think the functions look cool - a pivot table, is a much better solution in most cases - even more so if you combine it with Power Query and connection only (which works for non-Power Pivot pivotables)
This saves space and file size - agreed it's good for some charts you might want to create and for a cleaner look
Good points! It really depends on the situation but it's always good to have options. 😊
Thank you Leila, you always share amazing functions. Just wondering, if I have 10-20 PV tables in the worksheet, is that better to use these functions instead? Compare with pivot table, which one will slow down Excel? Thank you
Great video. Wondering how you would apply formatting to the result to make reading easier, problem is it is dynamic so formatting should also be dynamic?
@leilagharani - all the best for London, it was a pleasure having met you in person! 👍😎🤓
Excellent video very informative 😊😊
Hi Leila. You might want to send out a subscriber note that GroupBy crashes Excel. With me, at least a dozen times when attaching Slicers to filter the GroupBy results. This problem has been reported to MS but you might want to lend your voice to getting the bug fixed. Thanks for all you do.
Thanks for the feedback. The function is still in beta. I'm sure additional improvements based on the feedback from beta testers will be implemented.
Hi! How to like this video multiple times? Thank you so much for sharing this information. This is really a game and life changer.
Glad it was helpful!
🤩 These would have been so helpful a few months ago for a project I had that involved a lot of data!
Maybe for the next one. 😊
Thanks Leila, for youre programs and videos, but not always I could see them. Becouse of thime. Keep of showing vedios, and thanks, for youre work
Thanks for your support!
Thanks again for your wonderful tutorials Leila. I think =pivotby can be used to create dependant drop-down lists?
awesome video and functions! How would you show quarters using these functions (so something link 2023 - Q1 etc) without needing to add a Quarter column to the data?
Hi Leila. Thanks for the video. I'm curious, if there is any way to use slicers (one of my favorite features in Pivot tables) with GROUPBY/PIVOTBY?
These functions are realy amazing since I am starting to move away from Pivots due to their static nature. I have a couple of doubts however: do you need to apply these formulas to a range set as a Table and is it possible to return multiple values (sum and avergae for example)?
it would also be interesting to know if these functions are faster and/or "heavier" than Pivots