Using Excel’s GROUPBY function as a PivotTable alternative

Q. Are there any alternatives to traditional PivotTables in Excel?

A. The first thing that comes to mind is the function GROUPBY. The primary purpose of GROUPBY is to make data analysis easier by letting users group related data points and apply specific functions like SUM, AVERAGE, or COUNT to the groups. Unlike traditional methods, such as PivotTables, which often require a more manual setup, GROUPBY offers a simpler approach by allowing users to generate summaries directly within formulas. Another advantage of the GROUPBY function over conventional PivotTables is that the formula automatically refreshes when the source data changes.

The syntax for the GROUPBY function is =GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship]).

• Row_fields, which is required, tells Excel which column or range of values to use for grouping the rows. It determines how the data will be organized during analysis.
• Values, which is required, indicates the column or range of data that you want to combine. This is where you specify what numerical information will undergo the aggregation process.
• Function, which is required, defines how you want to combine the values. The choice of function affects how your summarized data is calculated. The functions you can choose are as follows:
  • SUM
  • PERCENTOF
  • AVERAGE
  • MEDIAN
  • COUNT
  • COUNTA
  • MAX
  • MIN
  • PRODUCT
  • ARRAYOFTEXT
  • CONCAT
  • STDEV.S
  • STDEV.P
  • VAR.S
  • VAR.P
  • MODE.SNGL
  • LAMBDA
• Field_headers, which is optional, determines whether headers should be included in the results. This can make it easier to understand by providing context for each grouped section.
  • 0 or omitted = No
  • 1 = Yes but don’t show
  • 2 = No but generate
  • 3 = Yes and show
• Total_depth, which is optional, specifies whether row fields should include totals and/or subtotals.
  • 0 or omitted = No totals
  • 1 = Grand totals
  • 2 = Grand and subtotals
  • -1 = Grand totals at top
  • -2 = Grand and subtotals at top
• Sort_order, which is optional, specifies how you want the rows to be sorted in the output. Choose the number of the returned column you want sorted. Make it a positive number if you want it sorted in ascending order, or negative if you want it sorted in descending order.
• Filter_array, which is optional, indicates which rows should be included or excluded from the output.
• Field_relationship, which is optional, specifies how relationships between fields are managed when handling multiple columns.

Currently, the GROUPBY function is available only in Microsoft Excel 365.

If you would like to follow along in using this function, you can download this Excel workbook and view the video at the end of this article.

Let’s work on a few examples using the GROUPBY function. See the small snippet of the spreadsheet we will use below. It contains 500 transaction IDs, customers, categories, months, and amounts.

Let’s start by simply grouping the transactions by category and calculating the total amounts for each category. This eliminates the need for a PivotTable. Some benefits to using GROUPBY over a PivotTable are that it is formula-based, it updates dynamically, and it is easy to use in reports (it does not require setup). In cell G1, enter the GROUPBY function, defining C2:C501 (all the categories) as the row_fields, E2:E501 (all the amounts) as the values, and SUM as the function. The formula will list each category only once, with the total of each category shown. You can see in the screenshot below how this looks similar to a simple PivotTable.

Next, let’s group the transactions by customer, then by client, and calculate the total amounts. In cell G1, again, enter the GROUPBY function, specifying B2:C501 (which contains all the customers and categories) as the row fields, E2:E501 (which includes all the amounts) as the values, and SUM as the function. The formula will display each customer, then each category within each customer, along with the total amount for each customer/category. See the formula and grouping below.

Now, let’s group the transactions by customer, then by client, and calculate the total amounts, but this time we will include the headers so that it is clearer to us and other users about what the groupings represent. In cell G1, specify B1:C501 as the row fields, E1:E501 as the values, SUM as the function, and 3 as the field headers. (Note I indexed row one in the first two arguments.) This instructs Excel that there are headers and to show them. See the formula and grouping on the next page that now contain headers.

Let’s group the transactions the same as we did in the example above, but now let’s include subtotals and a grand total. Define the arguments the same as above, but this time, specify 2 as the total_depth. See the formula and grouping on the next page, which now contains subtotals and a grand total.

Lastly, let’s create the same formula as we did in the example above, but now let’s sort the amounts in descending order within the customer/category groups. Define the arguments the same as above, but this time, specify -3 as the sort_order. See the formula and grouping in the next screenshot, with the amounts sorted in descending order.

You can create many types of groupings using the GROUPBY function, which is excellent for data analysis. It serves as a great alternative to traditional PivotTables.

About the authors

Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University. Wesley Hartman is the founder of Automata Practice Development.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org.