Nothing is more exciting than new functions in Excel, and if you agree with me on this, I have two pieces of good news for you today.
One is called GROUPBY and one is called PIVOTBY, and I think you are going to fall in love with both of them.
Microsoft 365
One word of caution: you will need to be using Microsoft 365 to have access to these functions. Unfortunately, these are not available in older versions of Excel.
Exercise File
If you would like to try out these functions on the data example I am using in my screenshots, I will include a workbook below. That said, there are many potential uses for these functions, so I am hoping this also inspires you for your own workbooks!
There are three sheets in this workbook: one sheet contains a space for you to try out these functions, and the other two contain the solutions for you to check your work.
GROUPBY
Excel’s new GROUBY formula will allow you to summarize a data set by category; most commonly this could be a sum of values, but you are not limited to this: you could just as easily use another function for calculation, like average, or count.
Here is an example with some data you were introduced to in Excel Pivot Tables sessions: a fictional student list.
From this list, I wanted to summarize the scholarships by college, so I built a simple GROUPBY function to accomplish this task:
Here is a closer look at that GROUPBY result:
Construction
The official syntax of this function is:
GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array],[field_relationship])
With these new functions there are always copious options, so don’t let yourself get overwhelmed by the potential number of arguments in this function.
My Example
In this example, I built the formula with only the first three values: Row, Values, and Function. You can click on this image for an enlarged view.
I built the function in cell F2 (yes it lives in one cell, more on this in a moment): =GROUPBY(B2:B40, D2:D40,SUM)
This translates to:
- B2 to B40: row fields
- D2 to D40: the values to calculate
- SUM:the function I want Excel to perform with those values.
From Microsoft
Here is some additional guidance from Microsoft. Note that only the first three fields are required. The rest are extras (although also worth checking out): GROUPBY function – Microsoft Support
PIVOTBY
PivotTables will always have a place in our hearts, but sometimes you prefer (or need!) to work with a formula. Now you can pivot without the table with the PIVOTBY function.
Here is that same starting data again:
Here is a PIVOTBY Function, with “college” set for rows and “sports team” set for columns:
Here is a closer look at that function result: a PivotTable without the table! 😲
Construction
The official syntax of this function is:
PIVOTBY(row_fields,col_fields,values,function,[field_headers],[row_total_depth],[row_sort_order],[col_total_depth],[col_sort_order],[filter_array],[relative_to])
My Example
I built this function in F2: =PIVOTBY(B2:B40,C2:C40,D2:D40,SUM)
Notice how it starts similar to GROUPBY but has an additional argument for column.
This is how this translates:
- B2 to B40: row fields
- C2 to C40: column fields
- D2 to D40: the values to calculate
- SUM: the function I want Excel to perform with these values
More detailed explanation of each potential argument can be found here: PIVOTBY function – Microsoft Support
As is the case with GROUPBY, not all fields are required. For instance, I only completed the first three in mt example: Row, Column, and Values.
Reminders/Tips for Working with New Functions
Here are a couple reminders for working with new or unfamiliar functions.
Function Arguments
After you create your function, but before you have entered values, go to the function arguments key (signified by “fx” next to your formula bar):
This is a great tool to walk you through unfamiliar fields, or just to allow you to explore the full field options. With both GROUPBY and PIVOTBY there will be a scroll bar with additional options.
Array Functions and #SPILL
Both GROUPBY and PIVOTBY are array functions, so you build them in one cell (F2 for instance), but they will spill into surrounding cells.
This means that, if something is in the way where Excel needs to spill content, you will see a #SPILL error. Deleting the content that is blocking the array to spill will resolve this error.
New Functions: Training Available
If this has inspired you to learn more about the new functions available in Excel 365, visit myTraining and look for a new course called Excel 365: New Functions.
This asynchronous course covers the new 365 functions XLOOKUP, UNIQUE, SORT, SORTBY, FILTER, and TEXTJOIN. It is designed as a supplement to the Excel: Advanced Formulas live sessions that are also listed in myTraining.
Check it out and let me know what you think!
