Using GROUPBY to Create a Summary Table

Tressa has a worksheet that includes raw customer sales transaction data for the western division of her company. Column E contains the state in which each customer lives, and column G contains the order amount for each transaction. She's been told that she can use the new GROUPBY function to create a simple summary table that shows sales by state, but she's unclear on exactly how to do this.

The traditional way to create a summary table is to create a PivotTable, but some of the newest functions in Excel have provided simple ways to get the summary without doing so. One of those functions is GROUPBY, which is currently only available in Microsoft 365. In Tressa's situation, the function would be quite easy:

=GROUPBY(E:.E,G:.G,SUM)

There are three parameters in this instance, all of which are required. The first is the values to be used for grouping. Since Tressa wants a summary by state, that is why column E is specified as a range for this parameter. The second parameter is the range of values to be aggregated. The first range and this second range should both have the same number of elements. Finally, the third parameter is the function to be used in doing the aggregating. In this case, the SUM function is specified because Tressa wants to add all the amounts together, by state.

The result of this is a two-column summary based on the states in column E, summing the values in column G. At the bottom of the summary is a grand total of all the states. The appearance of the summary table can be modified a bit by using some of GROUPBY's optional parameters. Personally, I like to add two parameters, as shown here:

=GROUPBY(E:.E,G:.G,SUM,3,0)

The fourth parameter (3) indicates if the source data has headers or not, as well as what to do with them. This can be a value of 0 (no headers), 1 (yes, no show), 2 (no, generate), or 3 (yes, show).

The fifth parameter (0) indicates if subtotals or totals should be included in the summary. Possible values are 0 (no totals), 1 (grand totals), 2 (grand and subtotals), -1 (grand totals at top), and -2 (grand and subtotals at top). If you leave this parameter off, then GROUPBY provides grand and subtotals according to its best guess.

If you want more info on the GROUPBY function, here are two good places to find it:

https://support.microsoft.com/en-us/office/5e08ae8c-6800-4b72-b623-c41773611505
https://exceljet.net/functions/groupby-function

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13965) applies to Microsoft Excel Excel in Microsoft 365.