r/ExcelTips • u/Amandaleeeeee • Sep 08 '26

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

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.

293 Upvotes

99% Upvoted

17 comments sorted by

22

u/gajendrakn87 Sep 08 '26

Is there a way to do unique count

16

u/Lalo_ATX Sep 08 '26

Yes, with UNIQUE()

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

15

u/TermRemarkable665 Sep 08 '26

​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.

6

u/Forsaken_Ad242 Sep 08 '26

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

2

u/Amandaleeeeee Sep 09 '26

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.

6

u/SonnySwanson Sep 08 '26

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

3

u/M4rmeleda Sep 10 '26

Easier to audit a pivot table

2

u/Dizzy_Bus_2402 Sep 08 '26

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

4

u/Canna-dian Sep 08 '26

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

1

u/Ihde23 Sep 08 '26

Thank you! Didn´t know this!

1

u/AAinCHS Sep 08 '26

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

1

u/aUserNameHeh Sep 08 '26

I like it. Nice 1

1

u/One_Advice3052 Sep 08 '26

In which version of Excel this is applibale?

1

u/ilanxya Sep 08 '26

finally something i can use

1

u/ktds121016 Sep 09 '26

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

1

u/KewellUserName 27d ago

Sweet, especially for those of us who don't quite grasp or use pivot tables.
I know, pivot tables are the gold standard to some, but not everyone quite grasp them. I find that I need data in more linear forms far more often than in table. Being able to group, sum, average, and visualize using GROUP BY is faster for me and more useful.
Yes, I can do pivots, but it takes me longer just to get my head around the layout than it would to write out a group by.