Excel - First Look at Amazing GroupBy PivotBy PercentOf Functions in Excel - Episode 2633

Microsoft Excel Tutorial: Three New Functions Debut in Excel: GROUPBY, PIVOTBY, and PERCENTOF.

Welcome to episode 2633 of MrExcel's netcast, where we explore the latest and greatest features of Microsoft Excel. In this episode, we will take a first look at three new functions: GROUPBY, PIVOTBY, and PERCENT OF. These functions are incredibly simple to use, yet offer deep options for data analysis. So let's dive in and see what they can do!

First up, we have GROUPBY. This function allows us to group data by categories and perform various calculations on the grouped data. We can choose from a variety of functions such as Sum, Percent Of, Average, and more. And the best part? We can even use our own custom lambda expressions for even more flexibility. With just a few simple arguments, we can quickly get a total by category or even multiple columns.

But that's not all, we can also add row fields to our GROUPBY function to get subtotals and grand totals. And for those who love sorting and filtering, we have options for that too. We can choose to sort by any column and even reverse the sort order with a simple minus sign. And if we want to filter out certain data, we can do that too with the filter array argument. With just three arguments, we can get powerful insights into our data.

Next up, we have PIVOTBY, which is similar to GROUPBY but with the added ability to add columns to our pivot table. We can choose which fields to display as rows and columns, and even add subtotals and grand totals. And just like GROUPBY, we can use our own custom lambda expressions for calculations. This function is perfect for creating dynamic and interactive reports that can be easily updated with new data.

Last but not least, we have PERCENT OF. While this function may seem simple, it plays a crucial role in the other two functions. It allows us to calculate the percentage of a value compared to the total. And when used in conjunction with GROUPBY or PIVOTBY, we can get even more powerful insights into our data. These functions are a game-changer for data analysis in Excel, and we have Joe McDaid and the Calc team at Microsoft to thank for them.

So if you want to take your data analysis to the next level, be sure to check out these new functions in Excel. And don't forget to like, subscribe, and ring the bell to stay updated on all our latest netcasts. If you have any questions or comments, feel free to leave them down below. Thanks for watching and we'll see you next time for another episode of MrExcel's netcast.

Table of Contents:
(00:00) Introduction of new functions: GROUPBY, PIVOTBY, and PERCENTOF
(00:10) Explanation of simplicity and options for GROUPBY
(00:20) Demonstration of GROUPBY with multiple columns and field headers
(01:05) Deeper exploration of GROUPBY with row fields and different totals options
(01:48) Sorting and filtering options for GROUPBY
(02:30) Reminder that any lambda can be used with GROUPBY
(02:40) Introduction to PIVOTBY and its similarities to GROUPBY
(02:50) Demonstration of PIVOTBY with categories, regions, and sales
(03:09) Expanding PIVOTBY with additional row fields and subtotal options
(03:48) Explanation of PERCENT OF function and its use in GROUPBY and PIVOTBY
(04:25) Reminder that these are all Eta-Lambdas and can be used in other functions
(04:39) Clicking Like really helps the algorithm
(04:49) Request for likes, subscriptions, and comments.

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #pivotby #groupby #percentof #excelnew #pivottable #pivot_table #excelpivot #excelpivottablestutorial #excelformula #excelformulasandfunctions

This video answers these common search terms:
how to create a pivot formula in excel
do percentages of a number in excel
replace pivot table with formula
substitute pivot table with formula
excel pivot table formula conversion
change excel pivot table to formula
replace pivot table results with formula
how to do percent of in excel

[LeilaGharani]

Love it! Thanks for the great video. Can‘t wait for these to show up in my beta version.

[Adam_K_W]

Holy COW Mr. Excel!! This is AMAZING! This is basically bringing Power Query functions out into the light of an average spreadsheet! Has anyone tried using these with data set up as a table so you can set the columns to table columns and automatically pick up new rows of data?

[vosk]

Wow, these are fantastic functions and much needed. We've been talking about the eta Lambdas for a while now. Thanks to MS for really stepping up their game over the last ~2 years.

[excelisfun]

Wow!!!! Thanks Mr Excel : ) : ) Does this mean we are no longer under nda lol

[GeertDelmulle]

WOW! These are great Excel times, always getting great new functions! Truly awesome!
Now we have GroupBy and PivotBy (just like in Power Query!) and with a full complement of arguments and sub-functions.
Now we can make Pivot reports that update in real time! So cool!
On top of that we now have eta-lambdas (again: just like in Power Query) and we can use them everywhere.
Thanks for the early warning, Mr. Excel.
We’ll use this like crazy. :-)
(Hope they’ll become mainstream soon, so we can share the results with our colleagues.)

[chrism]

Love the GROUPBY function, can't wait to get it, thanks Mr Excel!

[McKaySavage]

Wow, Excel is on such a streak of improvements. This will be super great to use once it’s in general release!

[jackcurl]

These three functions would account for about 80% of all pivot tables I've ever created, 98.6% of my current needs. Can't wait to get them and explore/exploit.

[Officeinstructor]

Hi Bill, glad I saw these functions in action in your tutorial because I'm not in the lucky 50%. Thanks for sharing

[ennykraft]

Fantastic new function! That's something really useful. Bye-bye SUMIF. Now all we need is the option to use the original formatting (which I wish all dynamic arrays had),

[chrysnotreally]

This is amazing. I just created a dashboard with multiple tables to get this similar result with groupby in 2 😂 I love your channel. Thank you!

[Quidisi]

it's like Christmas, every time new functions come out!!

[houstonvanhoy]

Posted an hour ago, 101 likes! Your fan club is online this morning!

Thanks for keeping us informed.

[MAFA.ACADEMY]

(This is amazing.I love your channel. Thank you!

[andrewcharlesmoss]

Great to see these new functions arrive!

[mattschoular]

Wow! Those are awesome. Thanks Mr.Excel

[ricos]

Ouch, these are good! I've already built pivot and group by lambdas, but these are better. Having SUM etc as pre-defined lambdas is genius and exactly what we needed when BYROW etc were released (that's not a criticism!). It'll make those functions so much easier to write.
Next up for the Excel guys - we know you watch all Bill's videos and religiously read the comments - is a format function. Something we can wrap a formula in and apply formats.

Bill, when you were testing this, did you notice if there was a way to reference the subtotal and totals rows within a custom lambda? That would be next level and really allow us to replicate DAX. For example, you could average at row level, but not sum at subtotal level.

[sandip_bettereveryday]

Thank you for enlightening us like always.

[KevinPGA]

WONDERFUL additions. Thank you Mr. Excel!!!

[piggybankaccountant]

Mister Excel just discovered you on linkedIn. Great video. 2600 episodes jesus.