Download the practice workbook 👉 HERE and follow along.
With GROUPBY, Excel users now have flexible, formula-based alternatives to traditional PivotTables—perfect for creating dynamic reports quickly!
What is the GROUPBY Function?
The GROUPBY function in Excel lets you organize data by category (like by manager or product) and find totals, averages, or counts for each group.
It’s easier to set up than a Pivot Table and updates automatically when your data changes.
How to Use GROUPBY in Excel?
GROUPBY is like a Pivot Table but simpler.
Here’s how it works:
- Pick Your Data to Group – Decide what you want to group. Some examples:
- Use Region if you want total sales in each area.
- Use Product to see the total quantity sold for each item.
- Use Sales Manager to compare individual performance.
- Choose a Value to Summarize – Choose a column with numbers to analyze. For example:
- Sales to find total sales in each group.
- Quantity to see how many units were sold per category.
- Cost to get an average cost by region.
- Select a Function – Pick the type of summary calculation. For example:
- SUM to get totals.
- AVERAGE for averages.
- COUNT to count items in each group.
Basic Example: GROUPBY
Let’s say you want to see total sales per Sales Manager for this dataset.
GROUPBY formula:
=GROUPBY(C1:C24, D1:D24, SUM)This formula in cell F1 groups by Sales Manager (column C) and sums the Sales (column E) for each one.
Automatic Totals with GROUPBY
When you use the GROUPBY function, Excel adds a Total row at the end of your results. In our example, it calculates the total sales for all sales managers combined.
This feature saves you time, as you don’t need a separate formula for grand totals.
Understanding ‘Spilling’ in GROUPBY
When you enter the GROUPBY formula in a single cell (like F1 in our example), Excel automatically “spills” the results into the cells below.
This means you don’t need to copy the formula to other cells—the entire summary table appears from that one formula.
If your data changes, the results update and resize automatically.
So, if you add new data (like a new region), it will appear in the spilled area. This makes GROUPBY perfect for creating dynamic summaries with minimal effort.
Calculation Options in Excel GROUPBY Function
The GROUPBY function in Excel offers a variety of aggregation options to help you analyze and summarize data in multiple ways.
Here is the list of available operations, along with a simple explanation for each:
Basic Aggregation Functions
- SUM: Adds up all values in each group. Use this to calculate totals, like total sales or revenue for each category.
- AVERAGE: Finds the average of values in each group. Great for calculating mean values, like average score or average price.
- COUNT: Counts numeric entries in each group. This is helpful if you want to know how many numeric items are in each category.
- COUNTA: Counts all entries, including text, in each group. Useful for getting the total number of records, including non-numeric items.
Statistical and Distribution Functions
- MEDIAN: Finds the middle value in each group. This is ideal when you need a central value that’s less affected by extreme values.
- MAX: Finds the highest value in each group, allowing you to identify top-performing sales, scores, or other maximum metrics.
- MIN: Finds the lowest value in each group, which can be useful for spotting minimum values like the smallest sale or score.
- MODE.SNGL: Returns the most frequently occurring value in each group. Great for finding trends, like the most popular product or item.
- PERCENTOF: Shows each value as a percentage of the total within its group. This is useful for understanding each item’s contribution to its category.
Advanced Aggregation Options
- PRODUCT: Multiplies all values in each group. This is commonly used for compound calculations, like cumulative growth rates.
- ARRAYTOTEXT: Converts the values in each group to text and outputs them as a single string. This is useful if you want to see all items in a group in one cell.
- CONCAT: Combines values in each group into one continuous string. It’s similar to ARRAYTOTEXT but is typically used for simpler concatenation.
Variability and Spread
- STDEV.S and STDEV.P: Calculate the standard deviation for a sample and a population, respectively. These functions show how spread out the data is around the mean, which is useful for variability analysis.
- VAR.S and VAR.P: Calculate the variance for a sample and a population, respectively. Variance measures data spread, and is often used in statistical analysis.
Custom Aggregation
- LAMBDA: Allows you to create a custom aggregation function. With LAMBDA, you can define any specific calculation that isn’t covered by the predefined options.
Using GROUPBY to Show Percentages
The PERCENTOF option in Excel’s GROUPBY function lets you show each group’s percentage of the total. This is useful when you want to see how much each category contributes to the overall sum.
Here’s an example formula:
=GROUPBY(C1:C24, D1:D24, PERCENTOF, 3, 1, -2)
Perform Multiple Aggregations at Once with GROUPBY
Want to analyze your data with multiple summaries side by side? Instead of creating separate GROUPBY formulas for each calculation, you can use HSTACK or VSTACK to display multiple summaries together in one view.
Let’s say, you want to see the sum, count, and percentage for each category of your dataset displayed side by side.
Here’s how to do it:
=GROUPBY(B2:B24, D2:D24, HSTACK(SUM, COUNT, PERCENTOF))
Featured Course
Master Excel’s Essential Modern Functions
Optional Arguments in GROUPBY
Optional arguments in GROUPBY let you customize how data displays and updates.
Field Headers: Show Header Information
With [field_headers] in GROUPBY, you can control if and how headers appear in your results. Here are the options:
- Automatic (default): Excel guesses based on your data.
- 0: No headers.
- 1: Use headers but don’t show them.
- 2: No headers in source data, so Excel creates them.
- 3: Use headers and display them.
Excel’s “Automatic” option assumes headers are present if the first value is text and the second is a number.
Example:
If you want to display the field headers, you simply add the optional argument “3“ to the formula:
=GROUPBY(C1:C24, D1:D24, SUM, 3)
💡 Tip: If you want headers displayed in your results, make sure to select them in the data you reference.
Total Depth: Include or Exclude Grand Total and Subtotals
With the [total_depth] argument the GROUPBY function can automatically include totals in your results.
There are several options for display:
- Automatic (default): Adds grand totals, and subtotals if possible.
- 0: No totals.
- 1: Grand totals only.
- 2: Grand totals and subtotals.
- -1: Grand totals displayed at the top.
- -2: Grand totals and subtotals displayed at the top.
Example:
You want a grand total and subtotals for division:
=GROUPBY(B1:C24, D1:D24, SUM, , 2)
💡 Tip: For subtotals, you’ll need at least two columns in your grouping field.
Sort Order: Sorting the Result
The [sort_order] argument lets you decide how rows display in the GROUPBY results.
Here’s how it works:
- Enter a Number: Use a number to select the column you want to sort by. For example, “1” sorts by the first column in your end result.
- Positive for Ascending, Negative for Descending: Positive numbers sort A-Z; negative numbers sort Z-A.
Example:
Let’s say you want to group by division and sort by sales value (third column) in descending order.
=GROUPBY(B1:C24, D1:D24, SUM, , 2, -3)
Filter Array: Filter Out Specific Rows
The [filter_array] option in GROUPBY lets you include or exclude specific rows in your results.
Example:
In this example, we have a sales dataset that includes unwanted “Total Manager” rows. These rows show total sales for each manager, but we don’t want them in our analysis.
Our goal: use GROUPBY to get sales by Division, excluding any rows that start with “Total.” Here’s the solution.
=GROUPBY(B1:B19, D1:D19, SUM, 3, 0, -2, LEFT(A1:A19, 5) <> "Total")How This Formula Works:
- B1:B19: Groups by Division.
- D1:D19: Sums Sales in each division.
- SUM: Adds up sales values.
- 3: Shows headers in results.
- 0: Turns off row-level totals.
- -2: Sorts descending by the sales amount.
- LEFT(A1:A19, 5) <> “Total”: Filter Array that excludes rows starting with “Total”.
With this setup, only relevant data rows appear in the results, making your summary clean and focused.
Field Relationship: Group Multiple Columns
The [field_relationship] option in Excel’s GROUPBY function controls how data is grouped when you use multiple columns.
It has two settings: Hierarchy and Table. Let’s look at each one and when to use them.
- Hierarchy (0 – Default): This option treats each column as part of a hierarchy. For example, if you group by Division first and then Sales Manager, Excel will sort by Division first, then by Sales Manager within each Division.
- Table (1): This option ignores hierarchy. Each column sorts independently, so there’s no nesting of categories. Note: Subtotals aren’t available with this setting.
Availability
The Excel GROUPBY Function is currently only available in Excel for Microsoft 365.
Download the Workbook
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the GROUPBY function in Excel with hands-on examples.
Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
