Excel GROUPBY Function – Summarize Data Without Pivot Tables

The Excel GROUPBY function is a modern dynamic array function that summarizes data by grouping records and performing calculations such as totals, averages, counts, minimums, and maximums.

Think of GROUPBY as a formula-based alternative to many Pivot Table summaries. It automatically spills results onto the worksheet and updates when the source data changes.

Why Use GROUPBY?

• Create summary reports with formulas instead of Pivot Tables
• Automatically expand and contract as data changes
• Build dynamic dashboards and reports
• Summarize by one field or multiple fields
• Combine with modern Excel functions such as FILTER, SORT, TAKE, and CHOOSECOLS

Basic Syntax

=GROUPBY(RowFields, Values, Function)

Argument	Description
RowFields	The column or columns used to group the records
Values	The numeric values to summarize
Function	The calculation to perform, such as SUM, AVERAGE, COUNT, MAX, or MIN

Example Data

Region	Salesperson	Sales
West	Jones	1500
East	Smith	2200
West	Brown	1800
East	Smith	1200
South	Davis	2500

Example 1 – Total Sales by Region

=GROUPBY(A2:A6,C2:C6,SUM)

This formula groups the records by region and calculates the total sales for each region.

Region	Total Sales
East	3400
South	2500
West	3300

Example 2 – Average Sales by Region

=GROUPBY(A2:A6,C2:C6,AVERAGE)

This formula calculates the average sales amount for each region.

Example 3 – Count Records by Region

=GROUPBY(A2:A6,C2:C6,COUNT)

This is useful for counting transactions, invoices, customers, or other records within each group.

Grouping by Multiple Columns

GROUPBY can summarize data by more than one field. In this example, the data is grouped by both region and salesperson.

=GROUPBY(A2:B6,C2:C6,SUM)

Region	Salesperson	Sales
East	Smith	3400
South	Davis	2500
West	Brown	1800
West	Jones	1500

Sorting GROUPBY Results

To sort the results, wrap GROUPBY inside the SORT function.

=SORT(
    GROUPBY(A2:A100,C2:C100,SUM),
    2,-1
)

This sorts the summary by the second column, largest to smallest.

Top 5 Groups

To return only the top results, combine GROUPBY with SORT and TAKE.

=TAKE(
    SORT(
        GROUPBY(A2:A100,C2:C100,SUM),
        2,-1
    ),
    5
)

This returns the top 5 groups based on the summarized amount.

GROUPBY with Excel Tables

GROUPBY works especially well with Excel Tables because the formula automatically expands as new data is added.

=GROUPBY(
    SalesData[Region],
    SalesData[Sales],
    SUM
)

Practical Accounting Example

For an accounts receivable report, GROUPBY can summarize open balances by customer.

=GROUPBY(
    AR[Customer],
    AR[Balance],
    SUM
)

This creates a live customer balance summary that updates as the A/R data changes.

GROUPBY vs Pivot Tables

Feature	GROUPBY	Pivot Table
Formula based	Yes	No
Dynamic spill results	Yes	No
Refresh required	No	Usually
Drill down	No	Yes
Good for dashboards	Yes	Yes
Best for ad hoc analysis	Sometimes	Yes

When to Use GROUPBY

• When you want a formula-based summary
• When the report should update automatically
• When you are building a dashboard
• When you want to combine summaries with other dynamic array formulas

When to Use a Pivot Table Instead

• When users need drill-down capability
• When users prefer dragging fields into rows, columns, and values
• When the report is exploratory rather than formula-driven

Common Functions Used with GROUPBY

Function	Purpose
SUM	Total amounts
AVERAGE	Average amounts
COUNT	Count records
MAX	Largest value
MIN	Smallest value
SORT	Sort the GROUPBY results
TAKE	Return the top or bottom results
FILTER	Filter data before summarizing

Key Takeaways

• GROUPBY is one of the most useful modern Excel functions for summaries.
• It can replace many basic Pivot Table reports.
• It works best with Excel Tables and dynamic array formulas.
• It is ideal for dashboards, management reports, and accounting summaries.

Bottom line: GROUPBY allows Excel users to create dynamic, formula-driven summaries that previously required Pivot Tables, helper columns, or VBA.