Excel GROUPBY & PIVOTBY Functions - All You Need to Know (do they BEAT Pivot Tables? 🤔)
Вставка
- Опубліковано 11 чер 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 am really blown away by how massively useful this new function is.
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!
Excellent delivery of knowledge..thanks a lot
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!
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 Leila, you are the best. These are very helpful formulas.
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.
As always Leila, you are great at explaining new features. Thank you.
Glad you enjoyed it. Thanks 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.
Wonderful tutorial on amazing new functions. Thank you, Leila!!!
Glad you like it!
happy New Year Leila thank you for all you do 🙂
Thank you Leila. I have learned so much from your videos
Explained beautifully as usual, you are the best👏👏 Thank you🙏😌
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!
Very Useful for me thanks Leila 💓
Can´t wait to get in touch with it !!!! Thank you, Leila !!!
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
Mam I'm grateful to you ... New function clubbed with other available function and your teaching way is awesome ... God Bless 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
Glad you like it!
Absolutely fantastic video! Your clear explanations made complex concepts feel so approachable. Looking forward to more insightful content from your channel!
Awesome, thank you!
amazing presentation thank You!!
you are the best Leila, best videos on you tube
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.
Very useful! Look forward using them
Yes - I need this!!! Super post (as always). Happy New Year!
Glad you like it! Happy New Year to you too!
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 !!
Excel is the most interesting thing that I found !! This Application is Awesome ❤❤❤
Excellent video very informative 😊😊
Excellent video madam. Great learning
Second video I watched on these two new functions, yours was easier to understand.
Totally needed this!!!! sharing it many of my buddies!!!!
Thanks for sharing it!
Thank you, Leila!
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.
This 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. :)
My pleasure! There should always be spreadsheet husbandry. 😁
Very good, structured and easy under stable teaching. Thanks.
Glad you liked it.
This is a powerful addition, nice to see Excel continue evolving
Excellent
❤
Very cool and useful
This is truly amazing !!!!! I love it. I have some great ideas that I am going to try at work now. :)
Brilliant. You are a great teacher! Thank you!
Thank you! 😃
Fantastic information. Thanks.
Our pleasure!
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.
Thanks verry much
Great video. I've wanted this functionality for a long time. Thanks so much.
Glad you like it!
Thank you very much for a clear, distinctive and useful explanation
My pleasure. Glad you liked it.
Absolute game-changers for excel freaks ! Thank you for the crisp and clear teaching!
Glad it was helpful!
Amazing 😊 Looking forward to try it 🎉
Teacher Leila, Your Teaching and your Materials as beautiful as your name is. Thank you for sharing.
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 UA-cam 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!
Awesome! Thank you!
Mouthwatering functions. can't wait
Very clear and helpful. Thank you so much for the video.
Our pleasure. Thanks for watching!
Thanks for this great video
I love it thank you Leila
I used macro to refresh my pivot tables. With Groupby and Pivotby, no need. Amazing function. Thanks, Leila!
You're welcome!
great functions
Thank you
Awesome tutorial
Great video thank you
Great explanation Leila!
Glad you think so!
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 appreciate your efforts in enhancing user capabilities through updates. Please provide guidance on utilizing the GROUPBY & PIVOTBY functions for multiple tables across several sheets.
was waiting for your tutorial on this , thank you for sharing
My pleasure. Thanks for watching!
Wow..thanks so much
AMAZING LESSON
Thank you!
مثل همیشه عالی بودین. ممنونم
You had me at live updates ❤
Thanks 👍🏻
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 awesome! I can't wait for the GROUPBY to be available. Thanks for letting us know ❤
Our pleasure!
Great for medium to small datasets.
Great video, you are a good teacher!
I appreciate that!
great updates, Leila
Thank you!
Finally, I have been dreaming of this. I used to use Power query do to do this.
Simply awesome
Muchas gracias por tu aporte, saludos desde Colombia 😊
Thanks!
Thank you for sharing this amazing video.
Our pleasure!
Great stuff!
Wow!, You are great! Your shorts shows me how meany time I have overworked....
Better future ahead 😉
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!
Excellent 🎉 thanks for sharing and great 👍 explanation as always...
My pleasure!
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 😅)
Wonderful Leila 😮😮
Awesome video and the t-shirt is spot on! 😃
😁
perfect as usual.
Awesome functions! I'm already thinking of use cases for my daily work tasks.
👍
You a Rockstar
🤩 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. 😊
HEY! this is a neat feature; it saves me a few clicks.