When it comes to data analysis, pivot tables are very useful, and many Excel pros vouch for them. However, they do not automatically update when the data changes, placing that easy-to-forget job on the user. Luckily, Excel has the GROUPBY function, which allows me to create something structurally similar to a pivot table but with dynamic capabilities.
Pivot tables are easy to create, as you can do so in just a few clicks. But I assure you that using the GROUPBY formula to analyze your data is not that hard—it can even change the way you write formulas from now on. Furthermore, you can use GROUPBY formulas with slicers by employing a neat workaround to instantly filter them as needed.
How to use the GROUPBY function in a formula
A great alternative to pivot tables
GROUPBY is an Excel function that allows you to group rows based on one or more columns and then aggregate them using other functions. It’s a dynamic array function, meaning it performs calculations on multiple cells and returns either a single result or multiple results. It was introduced in Excel 365, meaning it’s not available in Excel 2019 or older.
Here’s the syntax of the GROUPBY function with the required parameters:
=GROUPBY(row_fields, values, function)
The row_fields parameter specifies the category or column by which to group your data (e.g., date, month, sales, or department). The values parameter is the range with the values you want to aggregate. Finally, the function parameter is, well, the function that will aggregate the values (e.g., SUM, AVERAGE, and COUNT).
Now, suppose I have a table with sales data and want to determine the total sales for each employee. Here is what that formula would look like (notice I’m also including the headers in the function):
=GROUPBY(Table1[[
Essentially, the formula creates an array that lists every salesperson and their total sales. I selected the Salesperson and Sales columns for the row_fields and values parameters, respectively. For the function parameter, I used SUM.
I can easily achieve the same result with a pivot table by dragging the Salesperson field into Rows and the Sales field into Values. It will also be nicely formatted. However, if I change anything in the sample table, the GROUPBY function will automatically update the returned table.
You can select more than one column for the row_fields parameter in the GROUPBY function, but they will appear in the order they appear in the table. To order them however you want, you can use the HSTACK function.
=GROUPBY(HSTACK(Table1[[
About those optional parameters…
Take full advantage of the GROUPBY function
The basic GROUPBY function works well, but I quickly discovered that those optional parameters can spice things up even more.
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])
Here’s what each parameter does:
- field_headers: Allows you to specify if headers should be displayed or not. The value Missing (default) = Automatic, 0 = No, 1 = Yes but don’t show, 2 = No but generate, and 3 = Yes and show.
- total_depth: Allows you to specify whether to show subtotals and grand totals. The value Missing (default) = Grand totals and, where possible, subtotals, 0 = No totals, 1 = Grand totals, 2 = Grand totals and subtotals, -1 = Grand totals at top, and -2 = Grand totals and subtotals at top.
- sort_order: This parameter lets you specify how your results should be sorted. For instance, if you want to sort by the fourth column in the table in ascending order, you would enter 4. Entering -4 will sort it in descending order.
- filter_array: This allows you to filter the results, showing only rows that meet specified criteria.
- field_relationship: Specifies how to group data when you enter multiple columns in the row_fields parameter, with 0 (default) = Hierarchy and 1 = Table.
Here’s an example of the full formula in action:
=GROUPBY(Table1[[
The field_headers parameter is set to show the headers, while the total_depth parameter shows the grand totals at the top. The sort_order parameter sorts the results based on the second column (Sales) in descending order. The filter_array parameter uses conditional logic to display only those whose name (Salesperson column) is not Alice.
Using slicers with the GROUPBY function
Sort data with the click of a button
One thing that makes pivot tables so great is that they work with slicers. Slicers can also work with the GROUPBY function, but they require a helper column to facilitate filtering.
Since slicers work with tables, I have to first ensure the data is in a table format. Then, I created a column that will serve as the filter (I named it Filter, but you can call it anything). From there, I use the SUBTOTAL function to help me determine if a row is empty or not.
=SUBTOTAL(103, [@Salesperson])
I picked the COUNTA function (103) to use with SUBTOTAL because it ignores hidden rows—it returns 1 if the referenced cell is nonempty and visible, and 0 if it’s hidden or empty. I used the Salesperson column as the reference value because I know it will never be empty or hidden. Once I entered this formula in the first cell of the filter column, it was automatically added to the rest of the cells since this is a table.
I then created a slicer using the Salesperson column and moved it to a separate sheet. Afterward, I entered the Filter column in the filter_array parameter of the GROUPBY function so it can work with the slicer.
=GROUPBY(Table1[[
Now I use the slicer to filter the resulting array of the GROUPBY function. Keep in mind that it will also filter the source table.
The GROUPBY formulas are not a replacement for pivot tables
GROUPBY is an alternative to pivot tables, not a wholesale replacement—use it when you need dynamic, formula-based aggregations. Furthermore, it provides greater control via formulas and is more transparent and editable. I will miss the interactivity, convenience, and efficiency of pivot tables, but GROUPBY formulas seem to be capable of handling my data analysis needs for now.
