Excel GROUPBY & PIVOTBY Functions - All You Need to Know (do they BEAT Pivot Tables? 🤔)
HTML-код
- Опубликовано: 22 май 2024
- 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
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.
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
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.
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
Very nice use of the HSTACK function to create a contiguous set of row fields. Well done.
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.
Leila, you always explain things so well! Thank 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 !!
Excellent delivery of knowledge..thanks a lot
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. ❤
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.
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
Thank you Leila for all you do. I've learnt a lot from you. Keep it up!
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
As always Leila, you are great at explaining new features. Thank you.
Glad you enjoyed it. Thanks for watching!
Thank you Leila, you are the best. These are very helpful formulas.
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!
Fantástic and clear as always.Thanks a lot Leila👏👏👏👏🙏
I used macro to refresh my pivot tables. With Groupby and Pivotby, no need. Amazing function. Thanks, Leila!
You're welcome!
Wonderful tutorial on amazing new functions. Thank you, Leila!!!
Glad you like it!
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!
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 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.
Thanks again for this amazing and useful master class! I loved ❤ . Rain of blessing to you.
Thank you Leila. I have learned so much from your videos
Can´t wait to get in touch with it !!!! Thank you, Leila !!!
Mam I'm grateful to you ... New function clubbed with other available function and your teaching way is awesome ... God Bless You 🙏
happy New Year Leila thank you for all you do 🙂
Absolute game-changers for excel freaks ! Thank you for the crisp and clear teaching!
Glad it was helpful!
Absolutely fantastic video! Your clear explanations made complex concepts feel so approachable. Looking forward to more insightful content from your channel!
Awesome, thank you!
This is a powerful addition, nice to see Excel continue evolving
Totally needed this!!!! sharing it many of my buddies!!!!
Thanks for sharing 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.
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.
Amazing!! I loved these options!!!
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.
amazing presentation 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. 😁
Excel is the most interesting thing that I found !! This Application is Awesome ❤❤❤
Yes - I need this!!! Super post (as always). Happy New Year!
Glad you like it! Happy New Year to you too!
Very good, structured and easy under stable teaching. Thanks.
Glad you liked it.
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. 😊
Thank you very much for a clear, distinctive and useful explanation
My pleasure. Glad you liked 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! 😊👍
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!
Great video. I've wanted this functionality for a long time. Thanks so much.
Glad you like it!
Excellent video very informative 😊😊
Excellent
❤
This is awesome! I can't wait for the GROUPBY to be available. Thanks for letting us know ❤
Our pleasure!
Thank you, Leila!
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!
Fantastic information. Thanks.
Our pleasure!
Awesome! Thank you!
was waiting for your tutorial on this , thank you for sharing
My pleasure. Thanks for watching!
Amazing 😊 Looking forward to try it 🎉
Great explanation Leila!
Glad you think so!
This is awesome 👌
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.
Brilliant. You are a great teacher! Thank you!
Thank you! 😃
Finally, I have been dreaming of this. I used to use Power query do to do this.
Very clear and helpful. Thank you so much for the video.
Our pleasure. Thanks for watching!
Excellent video madam. Great learning
you are the best Leila, best videos on you tube
Very cool and useful
Wow!, You are great! Your shorts shows me how meany time I have overworked....
Better future ahead 😉
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 😅)
Mouthwatering functions. can't wait
This is truly amazing !!!!! I love it. I have some great ideas that I am going to try at work now. :)
I love it thank you Leila
Thanks for this great video
Awesome tutorial
Great stuff!
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.
Great video 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!
You had me at live updates ❤
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.
Awesome functions! I'm already thinking of use cases for my daily work tasks.
👍
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! 😺🥰⭐️💎
مثل همیشه عالی بودین. ممنونم
@leilagharani - all the best for London, it was a pleasure having met you in person! 👍😎🤓
🤩 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. 😊
Great video, you are a good teacher!
I appreciate that!
Muchas gracias por tu aporte, saludos desde Colombia 😊
😮 Amazing! Loved it…
Glad you liked it!!
Thank you for sharing this amazing video.
Our pleasure!
great updates, Leila
Thank 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.
perfect as usual.
Simply awesome
Great for medium to small datasets.
Thanks verry much
Thank you
Wow..thanks so much
Excellent 🎉 thanks for sharing and great 👍 explanation as always...
My pleasure!
great functions
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 :)