Microsoft Excel Tutorial: Three New Functions Debut in Excel: GROUPBY, PIVOTBY, and PERCENTOF.
To download the workbook from today: https://www.mrexcel.com/youtube/6jP7DflN4sw/
Read the Microsoft Announcement: https://techcommunity.microsoft.com/t5/excel-blog/new-aggregation-functions-groupby-and-pivotby/ba-p/3965765
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: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/
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.
#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