The GROUPBY function in Excel builds a live summary table from one formula: it groups rows by a field, aggregates a value column such as revenue with SUM, and spills the result onto the sheet. The summary updates when the source data changes, so you can stay inside the grid instead of jumping straight into a PivotTable.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowThat matters for anyone who likes formula-driven models, reusable templates, or report tabs that need to update automatically. If you already know PivotTables, think of GROUPBY as a more composable option for lighter summaries.
When should you use GROUPBY in Excel?
Use GROUPBY in Excel when you want a summary table that updates with the source data and remains part of your formula model. It is especially useful for dashboards, control sheets, and lightweight reporting where a PivotTable would feel heavy or awkward to position.
- You want a dynamic summary next to other formulas.
- You need the result to spill into a report area automatically.
- You prefer one formula over a separate PivotTable object.
How is GROUPBY different from a PivotTable?
GROUPBY differs from a PivotTable because it returns an array result in cells, while a PivotTable creates a separate reporting object. That sounds like a small distinction, but it changes how easy it is to combine the summary with modern array functions, feed it into charts, or layer extra logic around it.
For small teams, this often means simpler workbooks. You can keep the raw data in one Excel table, write one spill formula on a report sheet, and let the summary refresh itself when the table grows.
What is the simplest GROUPBY formula?
The simplest GROUPBY formula groups one field and aggregates one value field, such as total revenue by region from a sales table.
The exact syntax can evolve, so use the official help pane in your version of Excel. The underlying habit matters more: reference a clean table, choose a grouping field, choose a value field, and make the aggregation obvious.
- Keep the source data in an Excel table so new rows flow into the formula.
- Name the output area clearly so charts and commentary can reference it.
- Use one simple summary first, then layer sorting or extra dimensions afterwards.
=GROUPBY(Sales[Region], Sales[Revenue], SUM)
When is GROUPBY better than a PivotTable?
GROUPBY is better than a PivotTable when you want the summary to behave like any other formula result. You can nest it, wrap it, sort it, or place it exactly where you need it in a model or dashboard. That makes it easier to combine with MAP, SCAN, and REDUCE or feed it into a spill-based reporting layout.
It is also easier to keep version control over the logic because the summary rule is visible in the formula bar, not hidden behind multiple field settings.
| Task | GROUPBY | PivotTable |
|---|---|---|
| Spill summary into a custom dashboard area | Excellent | Possible, but less flexible |
| Fast ad hoc exploration with drag-and-drop | Fine | Excellent |
| Combining with other formulas | Excellent | Awkward |
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowWorked example: a monthly sales sheet
A small wholesale team stores every order in one table with columns for month, sales rep, region, revenue, and margin. They want a report sheet that updates itself when fresh orders are pasted into the source table.
GROUPBY lets them summarise revenue by region in one spill formula, then use a second formula to sort the result and a third formula to display only the top regions on the dashboard.
- Source table on one sheet.
- GROUPBY result on the report sheet.
- Chart linked to the spill range so the visual updates automatically.
If you want to build formulas like this with an instructor, try The Complete Excel Course with AI Integration on Coding Liquids.
Common mistakes
- Using messy ranges instead of a clean Excel table.
- Trying to build a complex multi-stage summary before the first simple version works.
- Comparing GROUPBY to PivotTables as if one must always replace the other.
When should you use a PivotTable instead?
Use a PivotTable instead of GROUPBY when you want quick drag-and-drop exploration or more familiar field controls for a wider team. If you want a direct comparison, the next best read is GROUPBY vs PivotTable.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowFrequently asked questions
What does GROUPBY do?
GROUPBY returns a grouped summary, such as total revenue by region, as a spilling array formula. The summary updates with the source data and stays part of your formula model rather than becoming a separate object, so you can reference it, chart it and wrap other formulas around it.
How is it different from a PivotTable?
GROUPBY is a formula result in cells, not a reporting object like a PivotTable. That makes it easy to nest, sort, wrap, chart, or place exactly where you want in a model or dashboard, and to combine with MAP, SCAN, and REDUCE. A PivotTable stays better for drag-and-drop exploration.
What is the simplest useful pattern?
The simplest useful pattern groups one field and aggregates one value: GROUPBY the region column and the revenue column with SUM, as in =GROUPBY(Sales[Region], Sales[Revenue], SUM). Start there, then add fields, sorting or a different aggregation once the first version works.
When does GROUPBY beat a PivotTable?
GROUPBY beats a PivotTable when the summary must sit in a precise place, feed another spill formula, power a dashboard, or stay visible as logic in the formula bar. It also refreshes automatically with no manual refresh step, which suits report tabs that update when the source table grows.
Can I group by more than one field?
Yes, you can group by more than one field. Pass multiple columns for the row groups, and use its arguments to add totals and control sort order. It scales from a one-field summary to a multi-level breakdown, but build the simple one-field version first so you can check the result before adding more.
When is a PivotTable still the better tool?
A PivotTable is still the better tool for open-ended exploration, quick regrouping by dragging fields, or when the audience knows classic Excel but not modern array formulas. It is also the familiar choice for a wider team that wants field controls rather than formula logic.
Related guides on this site
If you want to keep going without opening dead ends, these are the most useful next reads from this site.
- The Complete Excel Guide 2026: Formulas, AI Copilot, and Modern M365
- GROUPBY vs PivotTable in Excel: Which Should You Use?
- PIVOTBY Function in Excel: Build Pivot-Style Reports With a Formula
- CHOOSECOLS, CHOOSEROWS, TAKE, and DROP in Excel: Slice Data Faster
- How to Create a Pivot Table in Excel: Step-by-Step Tutorial
Official references
These official references are useful if you need the product or framework documentation alongside this guide.
