The GROUPBY function was introduced in March 2024 in Excel 365. It allows users to effortlessly create dynamic COUNTIF or SUMIF functions. This article explains its use and provides simple examples.

What Does the GROUPBY Function Do?

The GROUPBY function is going to change how we make reports in Excel! 😮

Before, we had to use functions like UNIQUE, COUNTIF, SUMIF, MIN, MAX, or FREQUENCY to build reports. Now, just one function does it all 😎.

Even better—it also lets you:

• Choose to show or hide headers
• Sort the data with or without keeping the hierarchy
• Add filters right in the result
• Do advanced calculations like percentages

Let’s explore how to build and use the GROUPBY function.

How to Write the GROUPBY Function

Here’s what you need to include in the GROUPBY formula:

1. Row labels: Specify the column for grouping, such as "Region."
2. Values to group: Indicate the column to calculate, like "Sales."
3. Calculation type: Choose from sum, count, max, or min.
4. Show headers: Decide if headers should appear in the result.
5. Grand total: Add a grand total if needed.
6. Sort order: indicate the columns index to sort (1, 2, 3, ...) and increasing ( + ) or decreasing ( - )
7. Filter your data: you can add a filter directly in your formula to focus on a specific type of data
8. Hierarchy or not: when you filter your data, you can specify if you want to keep the hierarchy or not.

Don't be afraid by the number of arguments. A tooltip helps you to fill each argument correctly 😉

Examples to Use the Function

#1: Count Sales by Region

In this example, we analyze sales by region using the GROUPBY function. Follow these steps to write the formula:

1. Select the column for the Region labels.
2. Select the column with the Sales data.
3. Choose the calculation type: COUNT.

This single formula gives the number of sales records for each region. Let’s compare it with the COUNTIF function:

• First, list each region manually or use the UNIQUE function.
• Then, create a COUNTIF formula for each region.

The GROUPBY function simplifies this process, saving time and reducing errors 😃👍

#2: SUM of Sales per Region

By simply changing the last argument to SUM, you can easily analyze total sales, replacing the need for SUMIF.

#3: How do you calculate a percentage with GROUPBY?

Calculating percentages is often a challenge for many Excel users. A common mistake is forgetting to lock the reference to the total value. And inside a Pivot Table, it’s even trickier. However, with the GROUPBY function, performing such calculations becomes remarkably simple and efficient.

To calculate a percentage, simply choose PERCENTOF as the third argument in the calculation. This makes the process clear and precise, eliminating the need for manual adjustments.

Select more than one column for your row label.

But the function is super clever. You can select more than one column in the row label. This produce exactly the same result as SUMIFS or COUNTIFS

Don't think the initial order of your column limits you. You can recreate a virtual array with the CHOOSECOLS function 😮

Apply a filter to your result.

The seventh argument of the function enables users to apply a filter directly to the final result. For example, it can be used to display only clothing items and their associated gender where the color is white.

Conclusion

The GROUPBY function simplifies data analysis in Excel, offering more flexibility than pivot tables.

• It’s dynamic, fast, and works directly in formulas.
• Group multiple columns, even when they’re not contiguous.
• In one single formula, you can build a full dashboard analysis

Start using the GROUPBY function today and enhance your Excel skills!