Excel GROUPBY Explained: Simplify Data Organization and Aggregation
Do you spend a lot of time working with data in Excel? Sorting, summarizing, and organizing information can feel slow. You might already know how to use outlines, subtotals, or pivot tables. But now there’s an even easier way.
Introducing the GROUPBY function—a simple formula that lets you group, summarize, sort, and filter your data all at once. With GROUPBY, you can get totals and subtotals without extra steps. You don’t need any complicated tools—just type this formula into your spreadsheet and see how quickly data analysis can become.
Table of Contents
Excel GROUPBY function
The GROUPBY function lets you group and summarize data based on one or more columns. You can also sort and filter the grouped data with ease.
Because GROUPBY is a dynamic array function, it can return many results at once, which automatically fill the nearby cells. This means the results update right away when your source data changes—no need to click refresh!
It works like a pivot table but is simpler, with no extra formatting needed. You can use it to quickly find totals, averages, counts, and more. It’s a great tool for working with large amounts of data.
Syntax
The syntax of the GROUPBY function is as follows:
The GROUPBY function can take up to seven different inputs, but you only need the first three to make it work.
Required Inputs (Must Have)
- Row_fields – The column(s) you want to group your data by.
- Values – The numbers or data you want to summarize.
- Function – How you want to summarize the data, like SUM, AVERAGE, COUNT, MIN, MAX, etc.
Optional Inputs (Extra Features)
- Field_headers – Decides if your dataset has headers and how to display them:
- 0 = No headers
- 1 = Yes, but don’t show them
- 2 = No headers, but create them
- 3 = Yes, and show them
- Total_depth – Controls whether totals and subtotals appear:
- 0 = No totals
- 1 (default) = Grand total at the bottom
- 2 = Grand total + subtotals at the bottom
- -1 = Grand total at the top
- -2 = Grand total + subtotals at the top
- Sort_order – Sorts data by column number:
- Positive numbers = Ascending order
- Negative numbers = Descending order
- You can also use an array of numbers to sort by multiple columns.
- Filter_array – Filters out specific rows based on a condition.
- Use a TRUE/FALSE logic (Boolean array) to decide which rows to keep.
Even though only three inputs are required, these extra options help customize your results and make your data analysis even more powerful! 🚀
Usage notes
Here are some simple tips to help you use the GROUPBY function and avoid common mistakes:
- Dynamic Formula Behavior
The GROUPBY formula updates automatically when you change existing data. But if you add new rows, it won’t update unless you include extra empty rows in your row_fields and values ranges. - Headers
If you don’t choose the field_headers option, Excel will guess if your data has headers.
Here’s how it decides:
– If the first value is text and the second is a number, Excel assumes the first row is a header. - Consistent Range Lengths
Make sure your row_fields and values ranges have the same number of rows.
If they don’t match, Excel might give you a #VALUE! error. - Auto-Formatting Results
The results from GROUPBY can look messy, especially with totals and subtotals.
To make it easier to read, try using Conditional Formatting to highlight groups, totals, or important values.
Basic Excel GROUPBY formula
Let’s keep it simple! Imagine you have a table with:
- Column A → Category
- Column B → Product
- Column C → Region
- Column D → Revenue
You want to calculate the total revenue for each product.
To do this, you will:
- Use B2:B22 (Product) as the row_fields (this groups your data).
- Use D2:D22 (Revenues) as the values (this is what you will add up).
- Choose SUM as the function to total the revenues.
This will give you a list of products, each appearing only once, along with their total revenue
The complete formula would look like this:
=GROUPBY(B2:B22,D2:D22,SUM,3)
You’ll see a list of products, with each type showing up only once, along with the total revenue for that type.
Because you didn’t use any optional settings, Excel uses the default options:
- No headers are shown
- Data is sorted in ascending order
- No filters are applied
- A total row is added at the bottom automatically
This example shows how the GROUPBY function can quickly group and summarize data, giving you a clear overview without needing complicated formulas or extra tools.
However, it doesn’t include column headers or formatting, which can make the results harder to read at first.
In the next examples, we’ll learn how to add headers, improve formatting, and use conditional formatting to make the data easier to understand and more visually appealing!
How to use GROUPBY function in Excel - formula examples
Now that you know the basics of the GROUPBY function, let’s look at some advanced examples to see how useful and flexible it is. We’ll learn how to pick the best way to summarize your data and how to use extra options like field_headers to add clear labels, total_depth to show detailed totals and subtotals, and sort_order to arrange your data just the way you want.
Choose aggregation function
Currently, the Excel GROUPBY function lets you choose from 16 predefined aggregation operations:
You can use classic summary functions like SUM, AVERAGE, MEDIAN, MIN, MAX, COUNT, and more.
Show headers
To show column headers in your results, use the field_headers argument in the GROUPBY function.
- If your dataset already has headers, set this argument to 3 (Yes, show them).
- If your dataset doesn’t have headers, you can set it to 2 (No, but create them). Keep in mind that the created headers will be generic names like “Row Field 1” or “Value 1.”
Here’s how you can modify our basic formula to include headers:
By doing this, your output will now include field headers, enhancing the readability and clarity of your grouped data. It’s a simple tweak that can have a substantial impact on how you and others interpret the results.
Group rows based on multiple columns
To group rows by more than one column, you can select a range with several columns in the row_fields option.
For example, to group data first by product (Column B) and then by region (Column C), use the range B2:C22.
To count how many products are in each group, pick COUNT as the function. Since COUNT works only with numbers, use a number column for the values option — like the Revenue column (D2:D22).
Here’s the complete formula:
=GROUPBY(B2:C22, D2:D22, COUNT)
This formula groups your data by product and region, and shows the number of products in each group.
Note that this example does not automatically add headers. Instead, the headers were manually entered in cells F2:H2.
This method is handy when you’re analyzing information across multiple categories and want a detailed drill down into your data.
Group rows based on non-adjacent columns
In large datasets, important columns might not be next to each other. To group by several columns, you can use the CHOOSECOLS function inside the GROUPBY function.
Here’s how it works:
- First, give CHOOSECOLS your entire data range (for example, A3:C22).
- Then, list the numbers of the columns you want to include. In this example, use 1 for category and 3 for region.
CHOOSECOLS(A3:C22, 1, 3)
Nest the above formula in GROUPBY, and you’ll get this result:
This approach allows you to group data based on non-contiguous columns effectively, no matter how your data is structured.
Include or exclude grand total and subtotals
The total_depth setting in the GROUPBY function decides if totals and subtotals show up in your results. Here’s an easy explanation of what each choice means:
- 0 → No totals.
- 1 (default) → Grand total at the bottom.
- 2 → Grand total at the bottom + subtotals under each group.
- -1 → Grand total at the top.
- -2 → Grand total at the top + subtotals above each group.
For example, if you want both grand totals at the bottom and subtotals below each group, set total_depth to 2.
=GROUPBY(A2:B22,D2:D22,SUM,3,2)
Filter out specific rows
To filter certain results, use the seventh option called filter_array. This option needs a list of TRUE or FALSE values that is the same length as your row_fields. TRUE means “keep this row,” and FALSE means “leave out this row.
For example, to remove rows with “Chair” product, use this logical expression:
=GROUPBY(B3:B22, D3:D22, SUM, , , , B3:B22<>”Chair”)
As you can see, our GROUPBY formula effectively filters out rows based on the condition specified – any row where a value in column B is “Chair” is excluded from the grouped results.
Perform multiple aggregations on the same data at once
If you want to study the same data with different calculations, you might write a different GROUPBY formula for each.
But there’s an easier way! Instead of many formulas, you can put all the calculations side by side in columns or rows.
To do this, use:
- HSTACK → If you want the results side by side (horizontally).
- VSTACK → If you want the results one below the other (vertically).
This way, you can compare different summaries easily in one view!
For example, to get the sum, average and percentage of total for Revenue and place the results across columns, use the HSTACK function:
To find the sum, average, min and max value for each project type and place the results across rows, use the VSTACK function:
Conclusion
In conclusion, getting the hang of the Excel GROUPBY function can really open up a world of possibilities for data analysts and professionals. Our examples have shown you how to tweak the function to suit your specific needs, and with a bit of practice, you’ll be able to make your data do some pretty amazing things 😊
Download Practice File
You can also practice this through our practice files. Click on the below link to download the practice file.
