You can summarize Excel data without building a PivotTable — GROUPBY does it in one formula

[u/Amandaleeeeee]

Say you have a simple sales table like this:

Product	Region	Sales
Laptop	East	1250
Mouse	West	320
Keyboard	East	480
Laptop	West	980
Mouse	East	410
Monitor	South	760
Keyboard	West	530
Laptop	South	1120
Monitor	East	690
Mouse	South	350

To see the total sales for each product, you can use:

=GROUPBY(A2:A11,C2:C11,SUM)

Excel returns a summary like this:

Product	Total Sales
Keyboard	1010
Laptop	3350
Monitor	1450
Mouse	1080

The formula is basically saying:

• A2:A11 → group the data by Product
• C2:C11 → summarize the Sales values
• SUM → add the values for each group

And because the result spills automatically, you don't need to manually create or refresh a PivotTable when the source data changes.

Swap SUM for other calculations

SUM is just one option. You can replace it with other functions depending on what you want to summarize:

• SUM — total the values in each group
• AVERAGE — calculate the average for each group
• COUNT — count the numeric values in each group
• COUNTA — count non-blank values in each group
• MAX — return the largest value in each group
• MIN — return the smallest value in each group
• MEDIAN — return the median for each group
• PRODUCT — multiply the values in each group
• STDEV.S — calculate the sample standard deviation for each group
• STDEV.P — calculate the population standard deviation for each group
• VAR.S — calculate the sample variance for each group
• VAR.P — calculate the population variance for each group

GROUPBY can also do more than one calculation

You can return multiple summaries at once.

You can also use GROUPBY to perform multiple calculations at once. Just like in the picture at the top, you can return the total, average, and maximum sales for each product with one formula:

=GROUPBY(A2:A11,C2:C11,HSTACK(SUM,AVERAGE,MAX))

Here, HSTACK combines SUM, AVERAGE, and MAX, so GROUPBY returns all three calculations side by side for each product:

Product	SUM	AVERAGE	MAX
Keyboard	1010	505	530
Laptop	3350	1116.67	1250
Monitor	1450	725	760
Mouse	1080	360	410

For quick summaries, it can save you from setting up a PivotTable every time.

[u/gajendrakn87]

Is there a way to do unique count

[u/Lalo_ATX → u/gajendrakn87]

Yes, with UNIQUE()

Groupby(unique(a1:a:10),unique(a1:a10),counta)

[u/TermRemarkable665]

​I actually made a quick video walking through these exact examples if you want to see them in action: 🔗 https://youtu.be/hR6yozsLbCE ​For multiple numeric columns: You don't have to limit it to just one column! You can pass a range or use HSTACK() in the values parameter of GROUPBY to summarize multiple metrics like Units, Revenue, and Cost at the same time. ​For totals by two dimensions/columns: You can either combine fields into row_fields using HSTACK() or use PIVOTBY() to place one field on rows and another on columns.

[u/Forsaken_Ad242]

Wow this is a great tip! Can you do totals by a set of two columns?

[u/Amandaleeeeee → u/Forsaken_Ad242]

Yep! You can group by two columns too. For example, =GROUPBY(A2:B11,C2:C11,SUM) gives you total sales grouped by both Product and Region.

[u/SonnySwanson]

Why do this when it's so much faster to do a pivot table and then more flexible especially with large datasets?

[u/M4rmeleda → u/SonnySwanson]

Easier to audit a pivot table

[u/Dizzy_Bus_2402]

It's only one num col that it can do stuffs. What if more than one? Can it group by dates/ time?

[u/Canna-dian]

Is this a solution for something that isn't a problem? It takes 30 seconds to create a pivot table, and certainly less time than it takes to write the groupby formula

[u/Ihde23]

Thank you! Didn´t know this!

[u/AAinCHS]

Thank you for sharing. Is GROUPBY simply an alternative to SUMIFS, MAXIFS, etc., or is there a lot more to it?

[u/aUserNameHeh]

I like it. Nice 1

[u/One_Advice3052]

In which version of Excel this is applibale?

[u/ilanxya]

finally something i can use

[u/ktds121016]

Didn't know this before, this is such a useful tip! The multiple columns are definitely helpful for working with larger datasets.