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
Пікірлер: 555
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
4 ай бұрын
Is there any estimate as to when they will be available?
@reng7777
4 ай бұрын
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
4 ай бұрын
@@UtuDudas That's my question as well
@jeffmunkynutz1568
4 ай бұрын
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
4 ай бұрын
i hope it will be soon
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
4 ай бұрын
The amount of new features and functions in the last couple of years is amazing. They're really doing a great job.
@dougmphilly
4 ай бұрын
probably the first practical use for lambda
@olliehopnoodle4628
4 ай бұрын
@@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
4 ай бұрын
@@olliehopnoodle4628 way above my paygrade. its the one function i struggle with.
@enigma8181
4 ай бұрын
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.
These are game changers in my opinion. The dynamic behaviour, especially for PivotBy is so useful.
@LeilaGharani
4 ай бұрын
👍
@BettyFL
4 ай бұрын
I agree completely
@DCHHolman
3 ай бұрын
When will it be native in Excel???@@LeilaGharani
Very nice use of the HSTACK function to create a contiguous set of row fields. Well done.
Leila, you always explain things so well! Thank you!
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.
Thank you Leila for all you do. I've learnt a lot from you. Keep it up!
Excellent delivery of knowledge..thanks a lot
Thank you Leila, you are the best. These are very helpful formulas.
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. ❤
Fantástic and clear as always.Thanks a lot Leila👏👏👏👏🙏
Thanks again for this amazing and useful master class! I loved ❤ . Rain of blessing to you.
I can’t believe I never saw your videos before. The clarity you display is unparalleled. Thank you for these tips!
@LeilaGharani
4 ай бұрын
Glad you like them!
@Stan_sprinkle
15 күн бұрын
welcome to the fan club. She's the greatest. Been following for years.
As always Leila, you are great at explaining new features. Thank you.
@LeilaGharani
4 ай бұрын
Glad you enjoyed it. Thanks for watching!
Can´t wait to get in touch with it !!!! Thank you, Leila !!!
Thank you Leila. I have learned so much from your videos
Mam I'm grateful to you ... New function clubbed with other available function and your teaching way is awesome ... God Bless You 🙏
Wonderful tutorial on amazing new functions. Thank you, Leila!!!
@LeilaGharani
4 ай бұрын
Glad you like it!
Very Useful for me thanks Leila 💓
Explained beautifully as usual, you are the best👏👏 Thank you🙏😌
Second video I watched on these two new functions, yours was easier to understand.
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
4 ай бұрын
Thanks for tuning in even on Friday! I hope some cake was also in the mix. 😊
@muhammadkaleem4676
3 ай бұрын
@@LeilaGharaniI need your back, please
Wowsers!! Thanks for the great tutorial!!! Love it!!! I needed to know this yesterday literally! Happy to know it now!🙌🏾
@LeilaGharani
4 ай бұрын
Glad you liked it. Thanks for watching!
Absolutely fantastic video! Your clear explanations made complex concepts feel so approachable. Looking forward to more insightful content from your channel!
@LeilaGharani
4 ай бұрын
Awesome, thank you!
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
4 ай бұрын
That's so nice! Thank you for watching.
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
4 ай бұрын
Glad the videos are helpful for you! Let's hope the functions will be rolled out soon.
happy New Year Leila thank you for all you do 🙂
Very useful! Look forward using them
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 a powerful addition, nice to see Excel continue evolving
Totally needed this!!!! sharing it many of my buddies!!!!
@LeilaGharani
4 ай бұрын
Thanks for sharing it!
Excel is the most interesting thing that I found !! This Application is Awesome ❤❤❤
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
4 ай бұрын
My pleasure! There should always be spreadsheet husbandry. 😁
amazing presentation 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
@LeilaGharani
4 ай бұрын
Glad you like it!
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
3 ай бұрын
@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.
Amazing!! I loved these options!!!
I used macro to refresh my pivot tables. With Groupby and Pivotby, no need. Amazing function. Thanks, Leila!
@LeilaGharani
4 ай бұрын
You're welcome!
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!
@LeilaGharani
15 күн бұрын
Glad it was helpful!
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 video. I've wanted this functionality for a long time. Thanks so much.
@LeilaGharani
4 ай бұрын
Glad you like it!
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
4 ай бұрын
Thanks, Vijay! I did slow down on KZread 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!
Yes - I need this!!! Super post (as always). Happy New Year!
@LeilaGharani
4 ай бұрын
Glad you like it! Happy New Year to you too!
Thank you very much for a clear, distinctive and useful explanation
@LeilaGharani
4 ай бұрын
My pleasure. Glad you liked it.
This is awesome! I can't wait for the GROUPBY to be available. Thanks for letting us know ❤
@LeilaGharani
4 ай бұрын
Our pleasure!
Brilliant. You are a great teacher! Thank you!
@LeilaGharani
3 ай бұрын
Thank you! 😃
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! 😺🥰⭐️💎
Thank you, Leila!
Thanks Leila! Looking forward to getting these functions; they're still not in my Excel 365.
@LeilaGharani
4 ай бұрын
I hope it doesn’t take too long for them to roll out.
Excellent video madam. Great learning
Amazing 😊 Looking forward to try it 🎉
Excellent video very informative 😊😊
This is truly amazing !!!!! I love it. I have some great ideas that I am going to try at work now. :)
Muchas gracias por tu aporte, saludos desde Colombia 😊
was waiting for your tutorial on this , thank you for sharing
@LeilaGharani
4 ай бұрын
My pleasure. Thanks for watching!
Very clear and helpful. Thank you so much for the video.
@LeilaGharani
4 ай бұрын
Our pleasure. Thanks for watching!
Finally, I have been dreaming of this. I used to use Power query do to do this.
Very good, structured and easy under stable teaching. Thanks.
@LeilaGharani
Ай бұрын
Glad you liked it.
This is probably the best new function I’ve seen. The multitude of uses without even thinking about it is staggering.
@LeilaGharani
4 ай бұрын
Agreed! It's really a great addition.
@muhammadkaleem4676
3 ай бұрын
@@LeilaGharanineed your back
you are the best Leila, best videos on you tube
Awesome! Thank you!
Teacher Leila, Your Teaching and your Materials as beautiful as your name is. Thank you for sharing.
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 😅)
Great explanation Leila!
@LeilaGharani
4 ай бұрын
Glad you think so!
I love it thank you Leila
Mouthwatering functions. can't wait
Awesome functions! I'm already thinking of use cases for my daily work tasks.
@LeilaGharani
4 ай бұрын
👍
Wow!, You are great! Your shorts shows me how meany time I have overworked....
@LeilaGharani
3 ай бұрын
Better future ahead 😉
Excellent ❤
🤩 These would have been so helpful a few months ago for a project I had that involved a lot of data!
@LeilaGharani
4 ай бұрын
Maybe for the next one. 😊
Thank you for sharing this amazing video.
@LeilaGharani
4 ай бұрын
Our pleasure!
Thanks for this great video
You had me at live updates ❤
Fantastic information. Thanks.
@LeilaGharani
3 ай бұрын
Our pleasure!
Great video, you are a good teacher!
@LeilaGharani
4 ай бұрын
I appreciate that!
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.
Great video thank you
Awesome tutorial
Very cool and useful
Thanks verry much
This is awesome 👌
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
3 ай бұрын
Our pleasure. Thanks for watching!
What a gamechanger. Saves so much time these two new features. Too bad this is not supported on Mac (yet)
Looks really useful and no more clicking Refresh on pivot tables
@LeilaGharani
4 ай бұрын
👍
Excellent 🎉 thanks for sharing and great 👍 explanation as always...
@LeilaGharani
4 ай бұрын
My pleasure!
Great for medium to small datasets.
Thank you Leila, you are a great teacher. The way you teach is very helpful 👍🏼🙏🏼
@LeilaGharani
4 ай бұрын
Thank you! 😃
The FILTER function is the one that I use the most from all the dynamic arrays functions. FILTER is the 🐐
@LeilaGharani
4 ай бұрын
Very high up on my list too. 😊
great functions
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!!!
@LeilaGharani
4 ай бұрын
Thank you! T-shirt is our own design :)
Wow..thanks so much
Awesome video and the t-shirt is spot on! 😃
@LeilaGharani
4 ай бұрын
😁
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
3 күн бұрын
That's great to hear.
Simply awesome
😮 Amazing! Loved it…
@LeilaGharani
4 ай бұрын
Glad you liked it!!
AMAZING LESSON
@LeilaGharani
Күн бұрын
Thank you!
Great stuff!