Quick Navigation
GROUPBY Function
Summary
The GROUPBY function dynamically groups and aggregates data in Excel, creating pivot table-like summaries directly in formulas. It combines grouping, aggregation, sorting, and filtering capabilities into a single powerful function for advanced data analysis.
Syntax
GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| row_fields | Array/Range |
Yes | Column-oriented array/range defining row group levels and headers |
| values | Array/Range |
Yes | Column-oriented array/range containing data to aggregate |
| function | Lambda/Function |
Yes | Aggregation function(s) like SUM, AVERAGE, COUNT, or lambda expressions |
| field_headers | Number |
No | Controls header display: 0=No, 1=Yes hidden, 2=No but generate, 3=Yes show (default: Automatic) |
| total_depth | Number |
No | Totals control: 0=No, 1=Grand, 2=Grand+Sub, -1=Grand top, -2=Grand+Sub top (default: Automatic) |
| sort_order | Number/Vector |
No | Sort by column index (negative=descending) |
| filter_array | Boolean Array |
No | 1D boolean array to filter rows (must match row_fields length) |
| field_relationship | Number |
No | 0=Hierarchy (default), 1=Table (independent sorting) |
Using the GROUPBY Function
GROUPBY revolutionizes data summarization by combining pivot table functionality into dynamic array formulas. Perfect for creating automated reports, dashboards, and analytical summaries without manual pivot table creation.
Common GROUPBY Examples
Sales Summary by Year
=GROUPBY(A2:A100, D2:D100, SUM)Groups sales data by year and shows total sales per year
Product Sales with Sorting
=GROUPBY(C2:C100, D2:D100, SUM, , , -2)Groups by product, sums sales, sorts descending by total sales
Multi-level Grouping
=GROUPBY((A2:A100,B2:B100), D2:D100, SUM, 3, 2)Groups by Year and Month, shows headers and subtotals
Multiple Aggregations
=GROUPBY(A2:A100, D2:D100, {SUM, AVERAGE, COUNT})Shows Sum, Average, and Count of sales by year in columns
Frequently Asked Questions
Common Errors and Solutions
#VALUE! error
Cause: row_fields and values arrays have mismatched dimensions
Solution: Ensure both arguments have same number of rows
#CALC! error
Cause: filter_array length doesn't match row_fields
Solution: Make filter_array same length as row_fields
Unexpected results with totals
Cause: Single column row_fields with subtotal requests
Solution: Use at least 2 columns in row_fields for subtotals
Notes
- Returns dynamic array - use spill range
- Supports vector of functions for multiple aggregations
- Negative sort_order numbers sort descending
- Automatic mode intelligently detects headers and totals
- Ideal replacement for many pivot table scenarios
Compatibility
Available in: Excel 365
Not available in: Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365