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? 😃
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.
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...
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. ❤
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
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 ... 😍😍😍
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 😅)
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.
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.
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 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 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 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 ❤️
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!
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.
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.
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.
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
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.
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! 😺🥰⭐️💎
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.
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.
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.
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
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. :)
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.
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!!!
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
Good morning! I am curious: How far in advance do you get the new releases? I'm asking g because I have a subscription to Office 365 but I do not yet have the GroupBy function (nor the Checkbox function for that matter)? Or, am I missing some "update" requirement?
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 RUclips 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!
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.
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
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
Hi looks very useful, similar to the new python features. I have 2 questions. 1. Is there any option to have a slicer style button when using group by or pivot by? 2. Are you able to show more than one result, I.e. show sum and percent by using one formula? Thanks
I didn't know that you can include the UNIQUE in the predefined ARRAYTOTEXT option with the LAMBDA environment, very nice. Definitely learnt someting new here. Cheers
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?
![Seungmin "그렇게, 천천히, 우리(As we are)" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/kAzmhLHePqU/mqdefault.jpg)
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.
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 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...
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. ❤
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!
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
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
I loved the table with Walter White, Jesse Pinkman, Christopher Moltisanti, etc. You are the best
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 😅)
Teacher Leila, Your Teaching and your Materials as beautiful as your name is. Thank you for sharing.
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.
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.
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 was problably my favorite video from you, and that says a lot
Thanks Leila for covering these amazing functions in depth.
I am really blown away by how massively useful this new function is.
I used macro to refresh my pivot tables. With Groupby and Pivotby, no need. Amazing function. Thanks, Leila!
You're welcome!
Leila, you always explain things so well! Thank you!
My encyclopedia of Excel .... watched this again as reference for my work .... thanks
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 !!
Your explanations are always the easiest to understand. Thank you.
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!
I've learnt almost your Excel course only from Udemy. Your courses are true gems :D
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 . 🎉
Finally, I have been dreaming of this. I used to use Power query do to do this.
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.
The lambda (λ) function is a really powerful addition to this very useful function. Many thanks for this excellent upload.
Thank you Leila for all you do. I've learnt a lot from you. Keep it up!
Mam I'm grateful to you ... New function clubbed with other available function and your teaching way is awesome ... God Bless You 🙏
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!
Absolute game-changers for excel freaks ! Thank you for the crisp and clear teaching!
Glad it was helpful!
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.
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.
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.
😁
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.
As always Leila, you are great at explaining new features. Thank you.
Glad you enjoyed it. Thanks for watching!
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
👍
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.
Excellent
❤
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!
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!
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.
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. 😊
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 a powerful addition, nice to see Excel continue evolving
What a gamechanger. Saves so much time these two new features. Too bad this is not supported on Mac (yet)
You had me at live updates ❤
Excel is the most interesting thing that I found !! This Application is Awesome ❤❤❤
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.
Great explanation! I'll try to use this at work this week. :D
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.
Great addition to the already versatile EXCEL functions
👍
you are the best Leila, best videos on you tube
This is awesome! I can't wait for the GROUPBY to be available. Thanks for letting us know ❤
Our pleasure!
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.
Fantástic and clear as always.Thanks a lot Leila👏👏👏👏🙏
Group by is awesome! Sometimes I work with a combination with SUM, FILTER, INDIRECT and UNIQUE to do the same it can do.
👍
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.
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
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. 😁
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!
Wonderful tutorial on amazing new functions. Thank you, Leila!!!
Glad you like it!
Explained beautifully as usual, you are the best👏👏 Thank you🙏😌
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 :)
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
Mouthwatering functions. can't wait
مثل همیشه عالی بودین. ممنونم
Good morning! I am curious: How far in advance do you get the new releases? I'm asking g because I have a subscription to Office 365 but I do not yet have the GroupBy function (nor the Checkbox function for that matter)?
Or, am I missing some "update" requirement?
The functions are still in beta. You need to join Office Insider to get them now.
@@LeilaGharani Thank you! I remember you saying that in another video, you know, after you said it again. 😊
I'm looking forward to the updates!
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 RUclips 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!
Thank you Leila. I have learned so much from your videos
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?
Can´t wait to get in touch with it !!!! Thank you, Leila !!!
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!
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.
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
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
Very Useful for me thanks Leila 💓
happy New Year Leila thank you for all you do 🙂
Wow!, You are great! Your shorts shows me how meany time I have overworked....
Better future ahead 😉
Totally needed this!!!! sharing it many of my buddies!!!!
Thanks for sharing it!
Hi looks very useful, similar to the new python features.
I have 2 questions.
1. Is there any option to have a slicer style button when using group by or pivot by?
2. Are you able to show more than one result, I.e. show sum and percent by using one formula?
Thanks
Great questions. Now I wonder the same.
Great questions. Now I wonder the same.
Great questions. Now I wonder the same.
Thank you Leila, you are the best. These are very helpful formulas.
Im going to try hard to break comfortable habits and see where I can work this in. Thank you!
Our pleasure! Let us know how it goes.
Leila is back!!
What would it be like if I wanted to put each treatment in a new tab and rename it, of course!?
🙂 For more advanced manipulations, I would just use Power Query.
Great explanation Leila!
Glad you think so!
Thanks again for this amazing and useful master class! I loved ❤ . Rain of blessing to you.
Group by can be used to create ingredient list from tables... I like it!
Very good, structured and easy under stable teaching. Thanks.
Glad you liked it.
Amazing!! I loved these options!!!
Yes - I need this!!! Super post (as always). Happy New Year!
Glad you like it! Happy New Year to you too!
Second video I watched on these two new functions, yours was easier to understand.
I didn't know that you can include the UNIQUE in the predefined ARRAYTOTEXT option with the LAMBDA environment, very nice. Definitely learnt someting new here. Cheers
That's great to hear.
Thank you very much for a clear, distinctive and useful explanation
My pleasure. Glad you liked it.
love that everything updates automatically without having to refresh the pivot table,
👍
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?