GROUPBY Function

The Excel GROUPBY function allows you to group, aggregate, sort, and filter data based on the specified fields.

GROUPBY is rolling out to users enrolled in the Windows and Mac Excel beta channels. You may shift to the Beta channel; if functionality is not available, it shall appear soon.

Table of contents:

  1. What is GROUPBY in Excel?
  2. How to use the GROUPBY function to aggregate data?
  3. GROUPBY Function Syntax and Arguments
  4. GROUPBY Function Examples

What is GROUPBY?

GROUPBY is a new dynamic array function in Microsoft Excel. The function uses three required and four optional arguments. GROUPBY allows you to create a summary of your data via a single formula. It supports grouping along one axis and aggregating the associated values. GROUPBY is lightweight, automatically updated, and can depend on the result of other calculations. Combine the function with other functions to create a percentage breakdown in seconds. For example, if you have a table of sales data, you can create a summary of sales by year.

How to use the GROUPBY function to aggregate data?

The function uses 3 required (and 4 optional) arguments.


=GROUPBY(row_fields, values, function, field_headers, total_depth, sort_order, filter_array)

To do a simple GROUPBY in Excel, you need 3 arguments:

  1. What to group by
  2. The values to aggregate
  3. The function you’d like to use for the aggregation

In the first example, we want to show how easy to create a Sales report by Location.



Select the column you want to group. Next, choose the column that contains values, in this case, Sales. Finally, choose a built-in function you want to use for the aggregation, in this case, sum.

How to use the GROUPBY function to aggregate data?

Okay, it was a simple example.

GROUPBY Function Syntax and Arguments

In this chapter, we’ll explain all required and optional arguments.

Row fields

A column-oriented array or range containing the values used to group rows and generate row headers. The array or range may contain multiple columns. If so, the output will have multiple row group levels.


Values is a column-oriented array or range of data to aggregate. The array or range may contain multiple columns. If so, the output will have multiple aggregations.


The function argument is an explicit or eta-reduced lambda (SUM, PERCENTOF, AVERAGE, COUNT, etc.) used to aggregate values. A vector of lambdas can be provided. If so, the output will have multiple aggregations. The orientation of the vector will determine whether they are laid out row- or column-wise.

required arguments

Field headers

A number specifies whether the row_fields and values have headers and whether field headers should be returned in the results. The possible values are:

  • 0“: No; the data does not have headers and field headers should not be shown in the result.
  • 1“: Yes, and don’t show; the field data has headers, but they should not be shown in the result.
  • 2“: No, but generate; the field data does not have headers, but field headers should be generated and shown in the result.
  • 3“: Yes and show; the data has headers and field headers should be generated and shown in the result.

Here is an example of how to show headers and totals using “3” as a 4th argument.

GROUPBY Excel function 4th argument

Good to know: Automatic assumes the data contains headers based on the values argument. If the 1st value is text and the 2nd value is a number, then the data is assumed to have headers. Field headers are shown if there are multiple row or column group levels.

Total depth

Determines whether the row headers should contain totals.

The possible values are:

  • Automatic without using the argument: Grand totals and, where possible, subtotals.
  • 0“: No totals; do not show Totals
  • 1“: Grand Totals; Show Grand Totals only
  • 2“: Grand and Subtotals
  • -1“: Gran Totals at the top
  • -2“: Grand Totals and Subtotals at Top

We want to show the Grand Totals and the Subtotals in the example. To do that, append the table and add a new category. We use the SalesRep and Product columns to demonstrate how the Totals work.

The formula looks like this:


Sort Order

The sort_order argument is a number indicating how rows should be sorted. Numbers correspond with columns in row_fields followed by the columns in values. Using positive numbers, you can sort the data by ascending order. The rows are sorted in descending/reverse order if the value is negative.

A vector of numbers can be provided when sorting based on only row fields.

Okay, back to the previous example! We want to apply an ascending order for column 2!



groupby excel order

Filter array

The last argument is the filter array. The filter_array is a column-oriented 1D array of Booleans that indicates whether the corresponding row of data should be considered. You can filter the result using logical expressions to decide which record to exclude.

If you want to exclude Texas in the Location column, simply use this expression.


filter array argument

GROUPBY Function Examples

In this section, we will demonstrate some useful GROUPBY function examples.

Combine GROUPBY with the PERCENTOF function

As mentioned above, GROUPBY can use 16 different functions as an argument, so it does not need to use LAMBDA to perform various calculations. Try to use the PERCENTOF function to create a breakdown.

Locate the third argument, replace the sum function with the PERCENTOF function, and press enter. The result looks great; now you have a percentage breakdown by location. Finally, as a result of the formula, you achieved this dynamic array without using any Pivot Tables. So, it is a stunning way to save time and keep your tables up to date.

groupby percentof

You can use other built-in functions like AVERAGE, MEDIAN, MIN, MAX, COUNT, and CONCAT. The method is the same as above; replace the “function” argument.

Additional resources

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.