Excel GROUPBY & PIVOTBY Functions - All You Need to Know (do they BEAT Pivot Tables? 🤔)

[@LeilaGharani]

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.

[@UtuDudas]

Is there any estimate as to when they will be available?

[@reng7777]

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!!

[@newmhelemish]

@@UtuDudas That's my question as well

[@jeffmunkynutz1568]

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? 😃

[@abdelazizallam]

i hope it will be soon

[@bobfish7699]

These are game changers in my opinion. The dynamic behaviour, especially for PivotBy is so useful.

[@LeilaGharani]

👍

[@BettyFL]

I agree completely

[@DCHHolman]

When will it be native in Excel???@@LeilaGharani

[@IsraelItzkowitz]

I can’t believe I never saw your videos before. The clarity you display is unparalleled. Thank you for these tips!

[@LeilaGharani]

Glad you like them!

[@Stan_sprinkle]

welcome to the fan club. She's the greatest. Been following for years.

[@calebroberts1673]

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.

[@rdatta]

Very nice use of the HSTACK function to create a contiguous set of row fields. Well done.

[@olliehopnoodle4628]

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.

[@LeilaGharani]

The amount of new features and functions in the last couple of years is amazing. They're really doing a great job.

[@dougmphilly]

probably the first practical use for lambda

[@olliehopnoodle4628]

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

[@dougmphilly]

@@olliehopnoodle4628 way above my paygrade. its the one function i struggle with.

[@enigma8181]

All things that Access did for years...

[@mike42441]

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 !!

[@ym10up]

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. ❤

[@chh8860]

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 ... 😍😍😍

[@LeilaGharani]

Thanks for tuning in even on Friday! I hope some cake was also in the mix. 😊

[@muhammadkaleem4676]

​@@LeilaGharaniI need your back, please

[@FJA---]

This is probably the best new function I’ve seen. The multitude of uses without even thinking about it is staggering.

[@LeilaGharani]

Agreed! It's really a great addition.

[@muhammadkaleem4676]

​@@LeilaGharanineed your back

[@cjstout0]

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

[@LeilaGharani]

Glad you like it!

[@mogilirajesh7135]

Excellent delivery of knowledge..thanks a lot

[@ifonseca13]

I loved the table with Walter White, Jesse Pinkman, Christopher Moltisanti, etc. You are the best

[@teweldetesfay279]

Teacher Leila, Your Teaching and your Materials as beautiful as your name is. Thank you for sharing.

[@growth2509]

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!

[@Achalinny]

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.

[@My_Mediterranean_Travels]

The lambda (λ) function is a really powerful addition to this very useful function. Many thanks for this excellent upload.

[@SamehSaad-u3c]

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💗

[@VincentLui-kt7dw]

I am really blown away by how massively useful this new function is.

[@chiengsaby2867]

I used macro to refresh my pivot tables. With Groupby and Pivotby, no need. Amazing function. Thanks, Leila!

[@LeilaGharani]

You're welcome!

[@VENKATRAMANIYER-g7b]

Mam I'm grateful to you ... New function clubbed with other available function and your teaching way is awesome ... God Bless You 🙏

[@dobeikwan3684]

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 😅)

[@rempe772]

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.

[@abbottkatz8830]

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?

[@tomdoe4962]

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.

[@Prometeo59]

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.

[@joshuamanampiu6489]

Your explanations are always the easiest to understand. Thank you.

[@benbowyang]

Leila, you always explain things so well! Thank you!

[@gavinvanderwesthuizen9941]

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 . 🎉

[@valseguin]

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 ❤️

[@LeilaGharani]

Glad the videos are helpful for you! Let's hope the functions will be rolled out soon.

[@captvo]

Absolute game-changers for excel freaks ! Thank you for the crisp and clear teaching!

[@LeilaGharani]

Glad it was helpful!

[@sunnygala7972]

Thanks Leila for covering these amazing functions in depth.

[@rpmcmurfee]

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.

[@RickGrime-tbc]

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.

[@VahabSahvi]

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.

[@d.v.v562]

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!

[@d.v.v562]

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

[@VENKATRAMANIYER-g7b]

My encyclopedia of Excel .... watched this again as reference for my work .... thanks

[@montyearl1688]

Thank you Leila for all you do. I've learnt a lot from you. Keep it up!

[@VideoAndrega]

As always Leila, you are great at explaining new features. Thank you.

[@LeilaGharani]

Glad you enjoyed it. Thanks for watching!

[@mahendraroliya8695]

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 !!

[@henryg5735]

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!

[@TheDigitalOne]

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! 😺🥰⭐️💎

[@kajola]

This was problably my favorite video from you, and that says a lot

[@Sam-fun-life]

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.

[@willd3rbeast]

Finally, I have been dreaming of this. I used to use Power query do to do this.

[@frankfernandez2690]

This is a powerful addition, nice to see Excel continue evolving

[@RahulGupta-sf2ce]

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

[@LeilaGharani]

👍

[@mohammadj.shamim9342]

Thank you so much Leila Sensei. I am amazed by the functionality of these function. I said goodbye to pivot tables and ifs functions.

[@LeilaGharani]

Our pleasure. Thanks for watching!

[@emilythechef]

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. :)

[@LeilaGharani]

My pleasure! There should always be spreadsheet husbandry. 😁

[@russellslater4037]

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

[@LeilaGharani]

That's so nice! Thank you for watching.

[@vijayarjunwadkar]

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! 😊👍

[@LeilaGharani]

Thanks, Vijay! I did slow down on KZbin 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!

[@jrobinson8898]

Wowsers!! Thanks for the great tutorial!!! Love it!!! I needed to know this yesterday literally! Happy to know it now!🙌🏾

[@LeilaGharani]

Glad you liked it. Thanks for watching!

[@nazarkamal8831]

Excel is the most interesting thing that I found !! This Application is Awesome ❤❤❤

[@Alazen.]

The FILTER function is the one that I use the most from all the dynamic arrays functions. FILTER is the 🐐

[@LeilaGharani]

Very high up on my list too. 😊

[@stephenbui490]

I've learnt almost your Excel course only from Udemy. Your courses are true gems :D

[@pedroaguiargomes2606]

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!!!

[@LeilaGharani]

Thank you! T-shirt is our own design :)

[@kirschnercomputertraining]

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.

[@LeilaGharani]

Wow, thank you!

[@Lydia-u1d]

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

[@joseadamadorna147]

Hi! How to like this video multiple times? Thank you so much for sharing this information. This is really a game and life changer.

[@LeilaGharani]

Glad it was helpful!

[@JijaTaheri]

Absolutely fantastic video! Your clear explanations made complex concepts feel so approachable. Looking forward to more insightful content from your channel!

[@LeilaGharani]

Awesome, thank you!

[@allanwatima9193]

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.

[@LeilaGharani]

😁

[@DanielViteripixplanet]

Thanks again for your wonderful tutorials Leila. I think =pivotby can be used to create dependant drop-down lists?

[@Annel-ExcelExceed]

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?

[@pt3470]

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

[@Prometeo59]

Great questions. Now I wonder the same.

[@Prometeo59]

Great questions. Now I wonder the same.

[@Prometeo59]

Great questions. Now I wonder the same.

[@marjovdn]

This is awesome! I can't wait for the GROUPBY to be available. Thanks for letting us know ❤

[@LeilaGharani]

Our pleasure!

[@MrLexhoya]

What a gamechanger. Saves so much time these two new features. Too bad this is not supported on Mac (yet)

[@HauiUSA]

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

[@LeilaGharani]

That's great to hear.

[@satishgopal2406]

Explained beautifully as usual, you are the best👏👏 Thank you🙏😌

[@francescox79]

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.

[@alejandroortiza8685]

I love your videos! Thanks for sharing! You should do videos about how to use outlook more effectively. In my company we get a bunch of emails and we need to follow up in some of them, then assign action items, have a follow up date, etc. It's a mess! If you are great at excel I believe you will be great at outlook as well!

[@LeilaGharani]

Glad you like the videos! About Outlook: maybe this will get you started: kzbin.info/www/bejne/a36VfKSVartlaqM

[@swapna_learner]

Thank you, Leila. what about the performance of formula compared to file size with pivot.

[@xyclos]

Fantástic and clear as always.Thanks a lot Leila👏👏👏👏🙏

[@ttsgdksl]

you are the best Leila, best videos on you tube

[@caiodaier]

this is super useful! I wonder when Excel will have a countunique in pivot tables (not only in power query). The only thing I’m missing to go back at excel

[@gandhisunil3]

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

[@LeilaGharani]

Unfortunately, Microsoft does not provide fixed ETAs. It will depend on the feedback of the users.

[@CP-ku4yx]

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

[@olliehopnoodle4628]

Second video I watched on these two new functions, yours was easier to understand.

[@butanaev]

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?

[@esmaric157]

@leilagharani - all the best for London, it was a pleasure having met you in person! 👍😎🤓

[@anujgupta3847]

Thanks a lot Leila.. appreciate your efforts in educating us…, when we expect this feature available in Office 365..?

[@eiestens]

Wow!, You are great! Your shorts shows me how meany time I have overworked....

[@LeilaGharani]

Better future ahead 😉

[@Callaghan_212]

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

[@KevinPGA]

Wonderful tutorial on amazing new functions. Thank you, Leila!!!

[@LeilaGharani]

Glad you like it!

[@bbubarker]

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

[@ColinMiller-wr2dx]

Leila - Thank you for such a great video and explanation of the new functions. I have sooo many uses for this. I have successfully used the HStack function to analyse multiple columns but I want to put the GroupBy table on a separate tab to the source data. I can do this when I'm not using the HStack function but as soon as I introduce the HStack function I keep getting errors. Do you have a solution to this please?

[@within_cells]

Im going to try hard to break comfortable habits and see where I can work this in. Thank you!

[@LeilaGharani]

Our pleasure! Let us know how it goes.

[@miless2111sutube]

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?

[@Aree-xz9ed]

Hi, Your videos are very effective and useful in our daily jobs. Can you help us to understand how to disable Research option in Excel, bcz this research option don't allow to type anything in Excel and we need to close the Excel and reopen to enable editing.

[@Saad.PS2009]

Thank you very much for a clear, distinctive and useful explanation

[@LeilaGharani]

My pleasure. Glad you liked it.

[@rleigh5204]

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.

[@LeilaGharani]

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.

[@TheRealNikita]

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?

[@LeilaGharani]

The functions are still in beta. You need to join Office Insider to get them now.

[@TheRealNikita]

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

[@hatemmedhat7172]

Hi Leila Thanks for ur valuable videos you've been sharing with us, am wondering if u have a full organised app scripts course from the scratch

[@LeilaGharani]

Not yet. It's on my to-do list though :)

[@Tkourtog]

Thank you Leila. I have learned so much from your videos

[@dorismagri1523]

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

[@LeilaGharani]

Thanks for your support!

[@teoxengineer]

Thank you Leila, I realized that total header is not showed in GroupBy function header. Why? And How can we use conditional formating in spilled table by using Group or Pivot By functions under subtotals and Grandtotals? And Can we use those functions in PowerQuerry or including VBA excel functions?

[@jorgelara3116]

Thanks again for this amazing and useful master class! I loved ❤ . Rain of blessing to you.

[@shaina85354]

I’m new to using Excel. I’ve purchased a few of your classes to assist me as a bookkeeper. I use Pivot tables for collect data for end of month from 2 accounting sources (QBO and Clio). Will these functions work in my line of work?

[@bluejeans706]

These are awesome! Is there a way to only show subtotals on rows that have more than one value? I don't want to exclude the values that only have one value, but it's messy to see a subtotal for a row that only has one value.

[@mariamilite]

Thanks for your helpful content. I have a question. Is there a possibility to use it for a case like the following: You have 4 columns. Column A is the ID which is empty but should be filled in. Column B is listing the entities. Column C lists the partner with which the entities have transactions. Column D the amounts of transactions. Note that if you filter for one entity and one parter entity,and at the same time you do the same things but in opposite, then the amounts go to zero.(For example: entity 1010 has transaction with partner entity 5040 of 200$. At the same time entity 5040 has transaction of -200$ with partner 1010.) The requirement is to add the same ID for all similar cases where entity can be the same partner also in the opposite way.Can we use “group by “?

[@khalidabdulraheem5056]

You always give us something amazing, Is there any possibility to see these new functions working with google sheet?

[@73p3ro]

Leila is back!! What would it be like if I wanted to put each treatment in a new tab and rename it, of course!?

[@LeilaGharani]

🙂 For more advanced manipulations, I would just use Power Query.

[@ferneychauxcoorado9691]

Muchas gracias por tu aporte, saludos desde Colombia 😊

[@axelgrind]

Great explanation! I'll try to use this at work this week. :D