Excel GROUPBY function to group rows and aggregate values
Do you work with lots of data in Excel? Sorting, summarizing, and organizing information can take time. You may have used features like outlines, subtotals, or pivot tables before. But now, there’s an even simpler way!
Meet the GROUPBY function – a powerful formula that helps you group, summarize, sort, and filter your data all in one step. You can also calculate totals and subtotals without extra effort. No need for complex tools—just use this formula right in your spreadsheet and make data analysis easier than ever!
Table of Contents
Excel GROUPBY function
The GROUPBY function helps you group and summarize data based on one or more columns. You can also sort and filter the grouped data easily.
Since GROUPBY is a dynamic array function, it can return multiple results that automatically spill into nearby cells. This means the results update instantly when your source data changes—no need to refresh anything!
It works like a pivot table, but without the extra formatting. You can use it to quickly calculate totals, averages, counts, and more, making it perfect for large datasets.
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 formula updates automatically when you change data in your current set. However, if you add new rows, the formula won’t update unless you include extra empty rows in your row_fields and values ranges. To fix this, convert your data into an Excel table so the formula can expand automatically. - Headers
If you don’t set the field_headers option, Excel decides if your data has headers by checking the first two values. If the first value is text and the second is a number, Excel treats the first row as headers. - Consistent Range Lengths
Make sure the ranges you use for row_fields and values are the same size. If they aren’t, you might see a #VALUE! error. - Auto-Formatting Results
The output from GROUPBY can be hard to read, especially when it includes totals and subtotals. Using conditional formatting can help highlight different parts of your data and make it easier to understand.
Which Excel version has GROUPBY function?
The GROUPBY is only available in Excel for Microsoft 365 (currently in the Insider: Beta Channel).
Basic Excel GROUPBY formula
Let’s keep it simple! Imagine you have a table with:
- Column A → Project names
- Column B → Project types
- Column C → Revenues
You want to calculate the total revenue for each project type.
To do this, you will:
- Use B2:B32 (Project Types) as the row_fields (this groups your data).
- Use C2:C32 (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 project types, each appearing only once, along with their total revenue
The complete formula would look like this:
You’ll see a list of different project types, with each type showing up only once, along with the total revenue for that type.
Since you didn’t change any optional settings, Excel uses its defaults: it shows no headers, sorts the data in ascending order, and applies no filters. Also, Excel automatically adds a total row at the bottom.
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 understand the basics of the GROUPBY function, let’s explore some advanced examples to see how flexible and powerful it can be. We’ll learn how to choose the right way to summarize your data and how to use extra options like field_headers for clear labels, total_depth for more detailed summaries, and sort_order to organize your data perfectly.
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.
There are also new functions you might find useful:
- PERCENTOF – Finds the percentage of a value compared to the total.
- ARRAYTOTEXT – Turns a list of values into text.
Eta Lambdas
Interestingly, the functions in the drop-down list look like regular Excel functions, but they actually work in a special way called eta-reduced lambdas.
This means you don’t need parentheses or extra arguments when using them. At first, this might seem unusual, but it makes things simpler. Instead of writing something long like LAMBDA(x, SUM(x)), you can just write SUM.
In addition to the built-in functions, you can also create and use your own custom LAMBDA functions. Check out the tutorial on how to do this with the GROUPBY function!
Multiple aggregations
You can apply multiple calculations at once by using a list of functions (called a vector of lambdas).
How the results appear depends on the direction of your list:
- Vertical list → Results will be stacked in columns (one below the other).
- Horizontal list → Results will be arranged in rows (side by side).
This lets you summarize data in different ways at the same time!
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 use a range that covers multiple columns in the row_fields argument.
For example, to group your data first by project type (Column B) and then by status (Column C), use the range B2:C32.
To count how many projects are in each group, choose COUNT as the function. Since COUNT only works with numbers, make sure to use a numeric column for the values argument – in this case, the Revenue column (D2:D32) works perfectly.
Here’s the complete formula:
This formula groups your data by project type and status, and shows the number of projects 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, the important columns might not be right next to each other. To group by multiple 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, A2:D32).
- Then, list the numbers of the columns you want to include. In this example, use 2 for project type and 4 for status.
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 argument in the GROUPBY function controls whether totals and subtotals appear in your results. Here’s a simple breakdown of what each option does:
- 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.
Filter out specific rows
To filter out specific results, use the seventh argument called filter_array. This argument takes a one-dimensional list of TRUE or FALSE values that matches the length of your row_fields range. A value of TRUE means “keep this row,” and FALSE means “exclude this row.”
For example, to remove rows with “Design” projects, use this logical expression:
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 “Design” is excluded from the grouped results.
Perform multiple aggregations on the same data at once
If you want to analyze the same data using different calculations, you could write a separate GROUPBY formula for each one.
But there’s a better way! Instead of using multiple formulas, you can place all the calculations next to each other 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:
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 😊
