Microsoft Excel Tutorial: Three New Functions Debut in Excel: GROUPBY, PIVOTBY, and PERCENTOF.
To download the workbook from today: Excel - First Look GroupBy PivotBy PercentOf Functions - Episode 2633 Sample Files - MrExcel Publishing
Read the Microsoft Announcement: New aggregation functions: GROUPBY and PIVOTBY
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.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
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:43) Clicking Like really helps the algorithm
(04:51) Request for likes, subscriptions, and comments.
To download the workbook from today: Excel - First Look GroupBy PivotBy PercentOf Functions - Episode 2633 Sample Files - MrExcel Publishing
Read the Microsoft Announcement: New aggregation functions: GROUPBY and PIVOTBY
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.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
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:43) Clicking Like really helps the algorithm
(04:51) Request for likes, subscriptions, and comments.
Transcript of the video:
Hey, just down below the video, if you click like that'll make sure that YouTube shows this video to more people. Thanks.
Wow, this is beautiful.
First, look at three new functions GROUPBY, PIVOTBY, and PERCENT OF.
They are remarkably simple to use but with deep options.
So GROUPBY, we want to group by the categories and I want to sum the sales.
Check this out - look at all these functions.
We have Sum, Percent Of, Average, all the way down through Mode.
You can put any lambda that you can write in here.
These are all really just shortcuts.
They call it an “ETA-lambda”.
Putting in SUM is really just a shorthand for writing a lambda to do the sum.
So there's our total by category.
Now check this out.
If I would specify two columns, so C and D, I get both sales and cost.
Wouldn't it be nice to have field headers?
Of course it would.
So comma, we have a choice here for no, yes but don’t show, generate, yes and show.
So that makes the most sense here in this case.
Alright, let's go deeper.
I'm going to put two row fields, so A and B.
And now we have a choice of what totals to use.
So here we can not say no totals, just the grand totals, which appears to be the default.
Grand totals and subtotals.
And Roger Govier in Wales is going to be thrilled with this: Grand totals at the top or grand and subtotals at the top.
I'll choose at the bottom like that.
So there's all of our fruit with a fruit total.
Herbs, total and vegetables total.
Ah, alright, this one: beautiful the sort order.
So are we going to sort by column one, column two, column three?
Let's sort by column three, which is sales.
And everything is arranged smallest to largest.
Well that's not how we want it, right?
Watch how elegant this is.
Minus three says go the other way and sort largest to smallest.
I love it.
The last thing they give us here is a filter array.
If for some reason I didn't want any herbs.
Then here in the filter array I just specify that the category is not equal to herbs like that.
And it gives us just fruit and vegetables.
Alright, simple enough, three arguments and you're there.
But plenty more arguments to improve it.
And don't forget that the SUM function can be replaced by any lambda that you can write.
PIVOTBY is just like GROUPBY.
But adds the ability to add columns.
So equal PIVOTBY, let's do a pivot table with categories down the left hand side.
So the row fields are category.
Across the top we will put region.
And in the values area sales.
And then the function, we will do a sum like that.
So there's our very simple little pivot report.
Let's try and expand this a little bit.
For the row fields, we'll do both category and product.
Alright, and so now we have for the headers, we're going to say yes and show.
For the rows, we want grand and subtotals.
Now we have a subtotal for fruit and herbs and vegetables.
This is incredibly cool.
The other choices here allow us to add more fields to the columns area and then control whether we have subtotals for the columns or not.
Also the sort order and the filter array.
The other function they gave us is called PERCENT OF which seems a little bit silly.
But they had to give us this in order to add “Percent Of” in GROUPBY and PIVOTBY.
So if we just say, Hey, this 1275 as a percentage of all of these numbers.
I'll process that four there and format as a percentage.
So it's a good way to get a percentage of the total.
But the place where they really expected us to use this would be in the which calculation part of GROUPBY or PIVOTBY.
Now, remember, these are all Eta-Lambdas.
Any of those functions that require a Lambda as the last argument.
For example BYCOL or BYROW.
Can now use these instead of having to write a Lambda in there, which adds even more power.
Thanks to Joe McDaid and the Calc team at Microsoft for giving us these great new functions.
And thanks to you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Wow, this is beautiful.
First, look at three new functions GROUPBY, PIVOTBY, and PERCENT OF.
They are remarkably simple to use but with deep options.
So GROUPBY, we want to group by the categories and I want to sum the sales.
Check this out - look at all these functions.
We have Sum, Percent Of, Average, all the way down through Mode.
You can put any lambda that you can write in here.
These are all really just shortcuts.
They call it an “ETA-lambda”.
Putting in SUM is really just a shorthand for writing a lambda to do the sum.
So there's our total by category.
Now check this out.
If I would specify two columns, so C and D, I get both sales and cost.
Wouldn't it be nice to have field headers?
Of course it would.
So comma, we have a choice here for no, yes but don’t show, generate, yes and show.
So that makes the most sense here in this case.
Alright, let's go deeper.
I'm going to put two row fields, so A and B.
And now we have a choice of what totals to use.
So here we can not say no totals, just the grand totals, which appears to be the default.
Grand totals and subtotals.
And Roger Govier in Wales is going to be thrilled with this: Grand totals at the top or grand and subtotals at the top.
I'll choose at the bottom like that.
So there's all of our fruit with a fruit total.
Herbs, total and vegetables total.
Ah, alright, this one: beautiful the sort order.
So are we going to sort by column one, column two, column three?
Let's sort by column three, which is sales.
And everything is arranged smallest to largest.
Well that's not how we want it, right?
Watch how elegant this is.
Minus three says go the other way and sort largest to smallest.
I love it.
The last thing they give us here is a filter array.
If for some reason I didn't want any herbs.
Then here in the filter array I just specify that the category is not equal to herbs like that.
And it gives us just fruit and vegetables.
Alright, simple enough, three arguments and you're there.
But plenty more arguments to improve it.
And don't forget that the SUM function can be replaced by any lambda that you can write.
PIVOTBY is just like GROUPBY.
But adds the ability to add columns.
So equal PIVOTBY, let's do a pivot table with categories down the left hand side.
So the row fields are category.
Across the top we will put region.
And in the values area sales.
And then the function, we will do a sum like that.
So there's our very simple little pivot report.
Let's try and expand this a little bit.
For the row fields, we'll do both category and product.
Alright, and so now we have for the headers, we're going to say yes and show.
For the rows, we want grand and subtotals.
Now we have a subtotal for fruit and herbs and vegetables.
This is incredibly cool.
The other choices here allow us to add more fields to the columns area and then control whether we have subtotals for the columns or not.
Also the sort order and the filter array.
The other function they gave us is called PERCENT OF which seems a little bit silly.
But they had to give us this in order to add “Percent Of” in GROUPBY and PIVOTBY.
So if we just say, Hey, this 1275 as a percentage of all of these numbers.
I'll process that four there and format as a percentage.
So it's a good way to get a percentage of the total.
But the place where they really expected us to use this would be in the which calculation part of GROUPBY or PIVOTBY.
Now, remember, these are all Eta-Lambdas.
Any of those functions that require a Lambda as the last argument.
For example BYCOL or BYROW.
Can now use these instead of having to write a Lambda in there, which adds even more power.
Thanks to Joe McDaid and the Calc team at Microsoft for giving us these great new functions.
And thanks to you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Last edited by a moderator:
