Excel's GROUPBY function: powerful data grouping and aggregation tools
Excel's GROUPBY
function allows you to group and aggregate data based on specific fields in the data table. It also provides parameters that allow you to sort and filter the data so that you can customize the output to your specific needs.
GROUPBY
function syntax
The GROUPBY
function contains eight parameters:
<code>=GROUPBY(a,b,c,d,e,f,g,h)</code>
Parameters a to c are required:
- a (row field): A range (one column or multiple columns) containing the value or category to which the data is grouped.
- b (value): The range of values containing aggregated data (one column or multiple columns).
- c (function): a function used to aggregate the values in parameter b .
Parameters d to h are optional, and you can learn more about these parameters in the last part of this article:
- d (field title): A number that specifies whether you selected the title in parameters a and b , and whether they should be displayed in the output.
- e (Total Depth): A number that determines whether the output should display the total.
- f (sorting order): a number that indicates how the results are sorted.
- g (Filter Array): An array-oriented formula used to filter out unnecessary information.
- h (field relationship): A number that specifies the field relationship when multiple columns are provided in parameter a .
GROUPBY
function practical: only the required parameters are used
If you are overwhelmed by a large number of parameters of the GROUPBY
function, it is important to note that GROUPBY
function works perfectly even if you only fill in the parameters a , b and c . So first, I'll show you how GROUPBY
function can use only these three parameters.
Suppose you have a restaurant chain that serves different dishes from different cuisines and you have calculated the total sales and average customer ratings for each cuisine-dish combination.
While these data are useful, you may be more interested in comparisons of different categories of data. Specifically, you might want to know the total revenue per cuisine and the average customer rating for each dish.
Then, since you want to see the total sales for each cuisine, select the cell that contains these data and add another comma:
<code>=GROUPBY(TabFood[Cuisine],TabFood[Sales],</code>
The last required parameter is the function used for aggregated data. In this example, since you want to find out the total sales of each cuisine, you need to insert the SUM
function and turn off the brackets:
<code>=GROUPBY(TabFood[Cuisine],TabFood[Sales],SUM)</code>
After pressing Enter, Excel calculates the average customer rating for each dish type. Again, without any optional parameters, the data is sorted alphabetically by default by the values in the column on the left, with a convenient total row at the bottom.
Since the values in column J are decimal averages, you can organize the displayed decimal places by clicking the "Increase Decimal places" and "Decrease Decimal places" buttons in the "Numbers" group on the Start tab.
GROUPBY
function practical: use optional parameters
Although the GROUPBY
function has five optional parameters in addition to the three required parameters, which makes it more complicated, these additional options are really just to help you create output that is more in line with your needs. More importantly, you can choose which optional parameters to use and skip unwanted parameters.
Below, I'll cover each optional parameter so you can see how they will affect your data when selecting to include them.
Field title
In my above example, I manually typed the output column headers because by default they are not included in the result. However, if you want the output data to contain the column title and the data it contains, use the field title parameter.
First type your GROUPBY
formula, including the first three (required) parameters. In this case, let's assume that you want to group the cuisines by average customer rating:
<code>=GROUPBY(A1:A21,D1:D21,AVERAGE</code>
Note that the title line is included in the selection. In fact, when selecting data for the first two parameters, you should consider in advance whether you want to output the data copy title in the table.
Benefits of including field titles | Disadvantages of including field titles |
---|---|
If you change the title in the original table, the output title will take these changes. | If you want to make the output title more specific than the original table title, you can't change the output title. |
Total depth
The Total Depth parameter allows you to decide whether you want the results to display a total, and if so, whether they should be at the top or bottom of the data. This parameter also allows you to choose whether to display a subtotal.
For Total Depth Parameters, type:
- 0, if you do not want any totals or subtotals to be displayed,
- 1. If you just want to display the total at the bottom of the result,
- 2. If you want the subtotal to appear at the bottom of each result category and display the total at the bottom of the entire result,
- -1, if you just want to display the total at the top of the result,
- -2, if you want the subtotal to appear at the top of each result category and display the total at the top of the entire result.
Sort order
The Sort Order field allows you to tell Excel whether and how to sort the results. Using this parameter does highlight why the GROUPBY
function is more useful than using a pivot table: as long as you change any data in the original table, the entire output data is reordered according to the sort order parameters, and the pivot table needs to be refreshed manually.
The number you enter for this parameter represents the column in the result. For example, if you type 1, this will sort the results for the first column in ascending or alphabetical order. On the other hand, typing -1 will sort the results of the first column in descending or inverse alphabetical order.
In this example, I've typed:
<code>=GROUPBY(A1:A21,C1:C21,SUM,,,,-2)</code>
This will sort the second column (sales) in descending order.
Filter arrays
Filtering array parameters are unlikely to be used like the previous optional parameters, although it can help if your original data table contains rows that may break the data.
In this example, the years in cells A2, A8, and A17 interrupt the result of GROUPBY
function.
I can use the filter array parameter to tell Excel to ignore any cell containing numbers in column A via the ISNUMBER
function:
<code>=GROUPBY(A1:A24,C1:C24,SUM,,,,ISNUMBER(A1:A24)=FALSE)</code>
Field Relationship
Finally, the field relation parameter controls how the data is grouped when the row field parameter refers to multiple columns.
In this example, when the field relationship parameter contains 0 (the default value if the parameter is omitted), GROUPBY
will return a hierarchical result table, where each column is represented by a separate data row.
<code>=GROUPBY(A1:B21,C1:C21,SUM,,,3,,0)</code>
On the other hand, when the field relationship parameter contains 1, GROUPBY
will return a result table that ignores the hierarchy and sorts each column independently. In other words, categories are not nested, which is why you can't include subtotals in the result when you select this field relationship option.
<code>=GROUPBY(A1:B21,C1:C21,SUM,,,3,,1)</code>
In addition to using SUM
and AVERAGE
in the GROUPBY
function parameters, you can also use the PERENTOF
function, which converts data into percentages to show the proportion of the subset that makes up the entire dataset.
The above is the detailed content of How to Use the GROUPBY Function in Excel. For more information, please follow other related articles on the PHP Chinese website!

Enhance Your Excel Charts: Reducing Gaps Between Bars and Columns Presenting data visually in charts significantly improves spreadsheet readability. Excel excels at chart creation, but its extensive menus can obscure simple yet powerful features, suc

Excel web version features enhancements to improve efficiency! While Excel desktop version is more powerful, the web version has also been significantly improved over the past year. This article will focus on five key improvements: Easily insert rows and columns: In Excel web, just hover over the row or column header and click the " " sign that appears to insert a new row or column. There is no need to use the confusing right-click menu "insert" function anymore. This method is faster, and newly inserted rows or columns inherit the format of adjacent cells. Export as CSV files: Excel now supports exporting worksheets as CSV files for easy data transfer and compatibility with other software. Click "File" > "Export"

Quick View of AVERAGEIF and AVERAGEIFS Functions in Excel Excel's AVERAGEIF and AVERAGEIFS functions can be used to calculate the average value of a dataset. However, unlike simpler AVERAGE functions, they are able to include or exclude specific values in the calculation. How to use the AVERAGEIF function in Excel Excel's AVERAGEIF function allows you to calculate the average value of a filtered dataset based on a single condition set. AVERAGEIF function syntax The AVERAGEIF function contains three parameters: =AVERAGEIF(x,y,z)

Excel's LAMBDA Functions: An easy guide to creating custom functions Before Excel introduced the LAMBDA function, creating a custom function requires VBA or macro. Now, with LAMBDA, you can easily implement it using the familiar Excel syntax. This guide will guide you step by step how to use the LAMBDA function. It is recommended that you read the parts of this guide in order, first understand the grammar and simple examples, and then learn practical applications. The LAMBDA function is available for Microsoft 365 (Windows and Mac), Excel 2024 (Windows and Mac), and Excel for the web. E

Quick Links Why Use the Camera Tool?

Master Microsoft Excel with these essential keyboard shortcuts! This cheat sheet provides quick access to the most frequently used commands, saving you valuable time and effort. It covers essential key combinations, Paste Special functions, workboo

Excel's PERCENTOF function: Easily calculate the proportion of data subsets Excel's PERCENTOF function can quickly calculate the proportion of data subsets in the entire data set, avoiding the hassle of creating complex formulas. PERCENTOF function syntax The PERCENTOF function has two parameters: =PERCENTOF(a,b) in: a (required) is a subset of data that forms part of the entire data set; b (required) is the entire dataset. In other words, the PERCENTOF function calculates the percentage of the subset a to the total dataset b. Calculate the proportion of individual values using PERCENTOF The easiest way to use the PERCENTOF function is to calculate the single

In Excel, using the timeline filter can display data by time period more efficiently, which is more convenient than using the filter button. The Timeline is a dynamic filtering option that allows you to quickly display data for a single date, month, quarter, or year. Step 1: Convert data to pivot table First, convert the original Excel data into a pivot table. Select any cell in the data table (formatted or not) and click PivotTable on the Insert tab of the ribbon. Related: How to Create Pivot Tables in Microsoft Excel Don't be intimidated by the pivot table! We will teach you basic skills that you can master in minutes. Related Articles In the dialog box, make sure the entire data range is selected (


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Zend Studio 13.0.1
Powerful PHP integrated development environment

SublimeText3 English version
Recommended: Win version, supports code prompts!

Dreamweaver Mac version
Visual web development tools

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools