=GROUPBY(array, by_array1, [by_array2], …, [by_arrayN], [reduce])One free generation or explanation per browser, shared across Excel and Google Sheets. Account usage limits apply afterward.
Need more than a single GROUPBY formula? Try our free AI for Excel — it reads your entire spreadsheet, answers questions in plain English, and builds charts and analysis in the same chat.
array:
The range or array of data that you want to group.
by_array1:
The first array or range that defines the grouping criteria.
by_array2:
The second array or range that defines additional grouping criteria (optional).
by_arrayN:
Additional arrays or ranges that define further grouping criteria (optional).
reduce:
A function that specifies how to aggregate the grouped data (optional).
The GROUPBY function in Excel is a powerful tool that allows you to group data by one or more columns and perform calculations on the grouped data. It helps you summarize and analyze large datasets by aggregating values based on specified criteria.
In simpler terms, the GROUPBY function helps you organize your data into meaningful categories, making it easier to analyze trends and patterns.
Imagine you are managing a sales report for a small business, and you have the following data in an Excel spreadsheet:
| Salesperson | Sales Amount | Region |
|---|---|---|
| Alice | 200 | East |
| Bob | 150 | West |
| Charlie | 300 | East |
| David | 250 | West |
| Eve | 100 | East |
You want to find the total sales made by salespeople in each region.
To calculate the total sales for each region, you would use the GROUPBY formula as follows:
=GROUPBY(A2:C6, C2:C6, "Total Sales", SUM(B2:B6))When you apply the formula, it groups the sales amounts by region and sums them accordingly. The output would be:
| Region | Total Sales |
|---|---|
| East | 600 |
| West | 400 |
Here, the total sales amount for the East region is calculated as 200 (Alice) + 300 (Charlie) + 100 (Eve) = 600, and for the West region, it is 150 (Bob) + 250 (David) = 400.
The GROUPBY function is particularly useful when you need to summarize large datasets based on specific categories without manually filtering or sorting your data. It allows you to quickly derive insights and make data-driven decisions.
Understanding how to use the GROUPBY function can significantly enhance your data analysis capabilities in Excel, enabling you to extract meaningful insights from your datasets.
Happy analyzing!
You have a table where Column A contains product categories and Column B contains sales figures for each category across different regions. You can use the GROUPBY function to summarize the total sales for each product category. This allows for a clear overview of which categories are performing well and aids in inventory management and marketing strategies without the need for complex pivot tables or manual calculations.
Beyond the GROUPBY function: Better Analyst is a full Excel AI that goes well past individual formulas. Upload any spreadsheet and ask questions in plain English — you'll get formulas, data analysis, and charts back in seconds. It's a free AI Excel assistant that works with any .xlsx, .xls, or .csv file — no plugins, no Microsoft 365 required.
Don't have a spreadsheet yet? Use our free AI spreadsheet generator to turn a text prompt into a downloadable Excel file — a full AI Excel sheet maker that builds headers, columns, and formulas from your description.
Create a free account for formula help, explanations, and spreadsheet analysis. Usage limits depend on your plan.
Browse our complete library of Excel formula guides
Compare your options
Open a neutral research prompt in the AI tool you already use. Compare capabilities, limitations, and fit before you choose.
AI answers can be incomplete. Check source links, current pricing, and product documentation before deciding.