Excel GROUPBY Function

What this does

GROUPBY aggregates a table by one or more grouping columns and returns the summary as a spilled array. The comparison that matters is against a PivotTable: a pivot must be refreshed after every data change and cannot be referenced reliably by position, while GROUPBY is a formula that recalculates like any other and can be nested inside SORT, FILTER or a chart source. It supports subtotals, custom sort order and a filter array, which covers most of what a simple pivot was used for. Microsoft 365 only, and rolled out gradually — it is absent from every perpetual release. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. Treat “excel groupby function” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected formula shows up while it is still harmless. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That turns a calculation you can defend to a CFO or an auditor into a method you can reuse, explain, and defend when the workbook leaves your screen.

A worked example

Revenue by region from a 5,000-row table: =GROUPBY(A2:A5000, D2:D5000, SUM) spills a two-column summary that updates as rows are added. With a grand total and descending order: =GROUPBY(A2:A5000, D2:D5000, SUM, 3, 1, -2). Two grouping levels: =GROUPBY(A2:B5000, D2:D5000, SUM). GROUPBY makes a grouped summary a formula rather than an object, which removes the refresh step that makes pivots go stale. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.

In Google Sheets

Some functions on this page are newer additions to Excel: they are in current Microsoft 365 and Excel for the web, while older perpetual Excel versions return #NAME?. Google Sheets maintains its own function list, so confirm each function exists there before relying on the same formula. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top, the tool proves it, and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Here is the takeaway for “excel groupby function”: copy the answer if you are busy, but if you have a spare few minutes, rebuild the example in Excel yourself with the tool above open beside it. That single pass — type it, run it, watch the result move when you change an input — is what turns a formula you found into a technique you trust. Keep your inputs labelled and referenced, never hard-coded, and the same sheet stays correct and auditable as it grows. Done that way, you will not need to look this up again, and you will be the person others ask.

Common mistakes

• Expecting it in Excel 2021 or an older perpetual version, where it returns #NAME?.
• Passing grouping and value ranges of different heights, which errors.
• Reaching for it where a real PivotTable is better — drill-down, slicers and manual grouping are all still pivot-only.

Frequently asked questions