Hi - Dave here.

Happy Friday!

One of the most interesting new functions in Excel is the GROUPBY function.

GROUPBY works like a lightweight pivot table, summarizing data by grouping rows together and applying various calculations.

The key difference is that GROUPBY works as a formula and automatically updates when data changes.

You can see how it works in the example below, where GROUPBY is summarizing over 200 rows of data by Region and Color. The formula in G4 looks like this:

=GROUPBY(B4:C226,D4:E226,SUM,3)

Summarizing over 200 rows of data with the GROUPBY function

[Download the workbook and read the full explanation]

The entire table is created with one formula. Unlike a Pivot Table, GROUPBY does not need to be refreshed; you will always see the latest information if the source data changes. On the other hand, GROUPBY does not apply any formatting, so you will need to apply formatting yourself. Click the link above for a full explanation of how to use GROUPBY. Then download the worksheet and try it out yourself.

Note: GROUPBY is only available in Excel 365.

What about Pivot Tables?

Should you still learn Pivot Tables? Yes, I think so. Even though GROUPBY and PIVOTBY can do many things a Pivot Table can do, Pivot Tables are still a better way to quickly explore data. Plus, Pivot Tables provide nice tools to automatically format the tables they create. For a good overview of Pivot Tables, including a workbook with sample data you can play around with, see this article.

Excel formulas

We maintain a list of over 1000 working formulas here.

If you need more structure, we also offer video training.

Have a great weekend!

Dave

The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at a specific Excel formula or function. Sign up on our home page.

 

Exceljet Logo
Exceljet
P.O. Box 4804
Salt Lake City, UT 84110

Copyright © 2024 Exceljet, All rights reserved.
You received this email because you are subscribed to our newsletter.
To unsubscribe, click the link below.