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

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Data analysis in Excel just got easier! Let me introduce you to the new Excel functions in Office 365: Excel GROUPBY and PIVOTBY functions. With these Excel functions you get to replace pivot tables with simple Excel formulas. In the video I cover the advantages of GROUPBY and PIVOTBY over pivot tables - for example the ability of GROUPBY to aggregate text values which pivot tables can't do. Also the ability to clean your data, for example remove total rows in the middle of your dataset or exclude certain rows with the Groupby and Pivotby functions. You also no longer need to refresh your data to get the latest result because formulas update automatically.
⬇️ Grab the workbook I use in the video here: pages.xelplus.com/groupby-piv...
In addition you'll learn about GROUPBY's optional arguments, like displaying headers, managing totals, and sorting data. A key highlight is its ability to process text values, offering unique text aggregations which is a notable advantage over Pivot Tables.
See GROUPBY in action as it tidies and analyzes complex data sets, easily filtering out unnecessary information for clearer reports. I also demonstrate how it efficiently groups sales data by year or month.
Finally, get to know PIVOTBY. This function is great for reducing vertical scrolling in reports, by shifting one or more of the categories to columns for a more concise and readable format.
Availability: GOUPBY & PIVOTY are currently available for Microsoft Insider Beta Channel only.
00:00 Why these new Excel functions completely beat Pivot Tables!
00:19 How to use Excel's GROUPBY function
02:19 How to use GROUPBY Optional Argument Field Headers
03:30 GROUPBY Optional Argument Total Depth
04:43 GROUPBY Optional Argument Sort Order
05:43 GROUPBY - How to Aggregate Text Values
07:27 How to Exclude Unwanted Rows with Filter Array
11:27 How to GROUPBY Years & Months
12:57 How to Combine Different Columns
13:57 How to use Excel's PIVOTBY function
16:14 Wrap Up
➡️ Join this channel to get access to perks:
/ @leilagharani
🎬 LINKS to related videos:
Excel LAMBDA - HOW & WHEN you Should use it: • Excel LAMBDA - HOW & W...
New Excel Functions: • Breaking News! We FINA...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/groupby-pivotby-function These functions will be available in Office 365. They are currently in Insider Beta version.

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

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

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

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

[@montyearl1688]

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

[@benbowyang]

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

[@soumire]

Thank you Leila, you are the best. These are very helpful formulas.

[@lukasolsovsky7299]

Can´t wait to get in touch with it !!!! Thank you, Leila !!!

[@xyclos]

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

[@jorgelara3116]

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

[@rdatta]

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

[@Tkourtog]

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

[@mogilirajesh7135]

Excellent delivery of knowledge..thanks a lot

[@satishgopal2406]

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

[@olliehopnoodle4628]

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

[@patriciaperezmagana8697]

happy New Year Leila thank you for all you do 🙂

[@cracknaturalinfo445]

Very Useful for me thanks Leila 💓

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

[@pulub]

Very useful! Look forward using them

[@KevinPGA]

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

[@LeilaGharani]

Glad you like it!

[@e.bmijaresmarquez323]

Amazing!! I loved these options!!!

[@zerzergil]

amazing presentation thank You!!

[@nazarkamal8831]

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

[@VideoAndrega]

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

[@LeilaGharani]

Glad you enjoyed it. Thanks for watching!

[@frankfernandez2690]

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

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

[@jayceeharb1305]

Totally needed this!!!! sharing it many of my buddies!!!!

[@LeilaGharani]

Thanks for sharing it!

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

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

[@notfarfromgone1]

Yes - I need this!!! Super post (as always). Happy New Year!

[@LeilaGharani]

Glad you like it! Happy New Year to you too!

[@ttsgdksl]

you are the best Leila, best videos on you tube

[@N82SV]

Great video. I've wanted this functionality for a long time. Thanks so much.

[@LeilaGharani]

Glad you like it!

[@VahabSahvi]

Excellent ❤

[@chakralamurali]

Excellent video madam. Great learning

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

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

[@ryanmiguelsantiago]

Thank you, Leila!

[@Saad.PS2009]

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

[@LeilaGharani]

My pleasure. Glad you liked it.

[@keshavamurthymm7035]

Excellent video very informative 😊😊

[@ArtfulData]

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!

[@jordiprochorov6901]

This is truly amazing !!!!! I love it. I have some great ideas that I am going to try at work now. :)

[@eaniluav]

Amazing 😊 Looking forward to try it 🎉

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

[@willd3rbeast]

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

[@teweldetesfay279]

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

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

[@drp2792]

Brilliant. You are a great teacher! Thank you!

[@LeilaGharani]

Thank you! 😃

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

[@farizg]

Awesome! Thank you!

[@erichu9876]

Very clear and helpful. Thank you so much for the video.

[@LeilaGharani]

Our pleasure. Thanks 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!

[@nirzohar]

Very good, structured and easy under stable teaching. Thanks.

[@LeilaGharani]

Glad you liked it.

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

[@user-dt1lt1wn7o]

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

[@unnikrishnansanthosh]

was waiting for your tutorial on this , thank you for sharing

[@LeilaGharani]

My pleasure. Thanks for watching!

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

[@wseemtouqan5310]

I love it thank you Leila

[@ferneychauxcoorado9691]

Muchas gracias por tu aporte, saludos desde Colombia 😊

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

[@marjovdn]

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

[@LeilaGharani]

Our pleasure!

[@RichKerr]

Great explanation Leila!

[@LeilaGharani]

Glad you think so!

[@gezaeglak]

Mouthwatering functions. can't wait

[@mhmdseadawy8228]

Thanks for this great video

[@rehanazizkhan5457]

Fantastic information. Thanks.

[@LeilaGharani]

Our pleasure!

[@manwendersingh5749]

This 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. 😊

[@Bhavik_Khatri]

Awesome tutorial

[@Sahnoun23]

Great video thank you

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

[@captvo]

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

[@LeilaGharani]

Glad it was helpful!

[@gijo401]

You had me at live updates ❤

[@kuuuyajim]

Awesome functions! I'm already thinking of use cases for my daily work tasks.

[@LeilaGharani]

👍

[@eiestens]

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

[@LeilaGharani]

Better future ahead 😉

[@Stevencyw88]

Very cool and useful

[@s.scottc.4051]

Great for medium to small datasets.

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

[@chiengsaby2867]

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

[@LeilaGharani]

You're welcome!

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

[@VeretenoVids]

🤩 These would have been so helpful a few months ago for a project I had that involved a lot of data!

[@LeilaGharani]

Maybe for the next one. 😊

[@SyedMuzammilMahasanShahi]

Thank you for sharing this amazing video.

[@LeilaGharani]

Our pleasure!

[@dstaedeli]

Great video, you are a good teacher!

[@LeilaGharani]

I appreciate that!

[@fcukccp]

Great stuff!

[@heoxam4215]

Thanks verry much

[@ifonseca13]

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

[@chrism9037]

Thanks Leila! Looking forward to getting these functions; they're still not in my Excel 365.

[@LeilaGharani]

I hope it doesn’t take too long for them to roll out.

[@arbazahmad7177]

Excellent 🎉 thanks for sharing and great 👍 explanation as always...

[@LeilaGharani]

My pleasure!

[@darlennysf]

😮 Amazing! Loved it…

[@LeilaGharani]

Glad you liked it!!

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

[@user-hx3ty4gl4g]

Wow..thanks so much

[@hi_vishy]

Simply awesome

[@esmaric157]

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

[@Sam-fun-and-entertaiment]

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.

[@pradyumnmishra2370]

great functions

[@mswordexpert]

perfect as usual.

[@mmhuq3]

Thank you

[@taxdad9729]

Looks really useful and no more clicking Refresh on pivot tables

[@LeilaGharani]

👍

[@sammad1149]

great updates, Leila

[@LeilaGharani]

Thank you!

[@MrLexhoya]

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

[@RZA12]

Thank you Leila, you are a great teacher. The way you teach is very helpful 👍🏼🙏🏼

[@LeilaGharani]

Thank you! 😃