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

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.

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:

🎬 LINKS to related videos:

🚩Let’s connect on social:

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]

These functions will be available in Office 365. They are currently in Insider Beta version.

[bobfish]

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

[olliehopnoodle]

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.

[calebroberts]

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.

[IsraelItzkowitz]

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

[VincentLui-ktdw]

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

[My_Mediterranean_Travels]

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

[FJA---]

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

[chrism]

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

[mike]

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

[ymup]

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

[chh]

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

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

[growth]

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!

[VENKATRAMANIYER-gb]

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

[captvo]

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

[cjstout]

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

[benbowyang]

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

[dobeikwan]

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