Summary

The Excel GROUPBY function is designed to summarize data by grouping rows and aggregating values. The result is a summary table created with a single formula.

Purpose 

Summarize data by grouping rows

Return value 

Data summary table

Syntax

=GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array])
  • row_fields - The values for grouping.
  • values - The values to aggregate.
  • function - The calculation to run when aggregating.
  • field_headers - [optional] 0 = No, 1 = Yes, don't show, 2 = No, generate, 3 = Yes, show.
  • total_depth - [optional] Totals and subtotals. 0 = No, 1 = Grand Totals, 2 = Both, -1 = Grand Totals at top, -2 = Both at top.
  • sort_order - [optional] Sort by index number. Negative numbers = descending order.
  • filter_array - [optional] Logic to remove specific rows.

How to use 

The GROUPBY function is designed to summarize data by grouping rows and aggregating values. The result is a dynamic summary table created with a single formula. The output from the GROUPBY function is similar to the output from a Pivot Table but without formatting. The summary returned by the GROUPBY function is fully dynamic and will immediately recalculate when source data changes.

The GROUPBY function is similar to the PIVOTBY function. The difference is that PIVOTBY can group by row and column, whereas GROUPBY can group by row only.

Basic Example

The GROUPBY function takes seven arguments but only the first three are required. In the worksheet shown below, we use the GROUPBY function to summarize sales by city. The formula in cell F5 is:

=GROUPBY(B5:B16,D5:D16,SUM)

GROUPBY function basic example - sum of sales by city

For this example, GROUPBY is configured with three arguments as follows:

  • row_fields - provided as the range B5:B16, which contains city names.
  • values - provided as the range D5:D16, which contains sales amounts.
  • function - provided as SUM, the calculation to perform during aggregation.

With the inputs above, the GROUPBY function summarizes sales by city and outputs the table in F4:G8 in one step. Notice that we have not included the header row in row_fields or values, because we are not asking GROUPBY to generate a header automatically. Instead, we have manually entered the header row in F4:G4. Note also that GROUPBY includes a Total row by default.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.