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)
[
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.