Excel GROUPBY and PIVOTBY functions

Download the GROUPBY Practice Workbook

Get the sales data from the video and build every summary yourself: totals by manager, percentages, filters, monthly groups and a PIVOTBY report.

What is the Excel GROUPBY Function? (Pivot Table Alternative)

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: Syntax & Basic Examples

GROUPBY is like a Pivot Table but simpler.

Here’s how it works:

  1. 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.
  2. 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.
  3. 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.

Excel sales data table with Date, Division, Sales Manager, and Sales columns used as GROUPBY source data

GROUPBY formula:

=GROUPBY(C1:C24, D1:D24, SUM)

This formula in cell F1 groups by Sales Manager (column C) and sums the Sales (column D) for each one.

Excel GROUPBY formula =GROUPBY(C1:C24,D1:D24,SUM) returning total sales per Sales Manager with a grand Total row

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.

Mastering Aggregations: SUM, PERCENTOF, and Custom LAMBDAs

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.

Example: Concatenate Text Values with ARRAYTOTEXT

To list all items in each group as a comma-separated string instead of summing numbers:

=GROUPBY(A2:A100, B2:B100, ARRAYTOTEXT, , 0)

This returns each category with all its values joined into one cell. The default delimiter is a comma and space. For a custom delimiter, use a LAMBDA with TEXTJOIN:

=GROUPBY(A2:A100, B2:B100, LAMBDA(x, TEXTJOIN("; ", TRUE, x)), , 0)

If a name appears more than once in a group, ARRAYTOTEXT repeats it. To list each name only once, wrap it in a LAMBDA with UNIQUE:

=GROUPBY(A2:A100, B2:B100, LAMBDA(x, ARRAYTOTEXT(UNIQUE(x))), , 0)
Syntax Tooltip: GROUPBY function autocomplete showing available aggregation options including SUM, PERCENTOF, and LAMBDA

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)
Excel GROUPBY with PERCENTOF returning each Sales Manager's percentage share of total sales in a spilled results table

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))
Excel GROUPBY with HSTACK combining SUM, COUNT, and PERCENTOF into a single side-by-side multi-aggregation results table

Featured Course

Master Excel’s Essential Modern Functions

FILTER, SORT, UNIQUE, XLOOKUP, SEQUENCE. The Excel functions most professionals were never taught, and the ones that turn hours of work into minutes.
Learn More
Excel new functions course cover

Advanced Settings: Headers, Total Depth, and Sort Order

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.

Syntax Tooltip: GROUPBY formula autocomplete showing field_headers argument options from 0 to 3 for header display control

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)
Excel GROUPBY formula with field_headers set to 3 returning a summary table with Sales Manager and Sales column headers visible

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.
Syntax Tooltip: GROUPBY formula autocomplete showing total_depth argument options for grand totals and subtotals placement

Example:

You want a grand total and subtotals for division:

=GROUPBY(B1:C24, D1:D24, SUM, , 2)
Excel GROUPBY formula with total_depth set to 2 returning subtotals by Division and a Grand Total row at the bottom

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)
Excel GROUPBY formula with sort_order set to -3 returning results sorted by the Sales column in descending order

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.

Excel source data table with Total Manager rows highlighted showing the rows to be excluded using the filter_array argument

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”.
Excel GROUPBY with filter_array using LEFT(A1:A19,5)<>"Total" returning a clean Division summary with Total Manager rows excluded

With this setup, only relevant data rows appear in the results, making your summary clean and focused.

GROUPBY’s filter_array uses the same Boolean logic as the FILTER function. For more on building complex filter conditions, see How to Use the FILTER Function with Multiple Criteria.

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.

Group Dates by Year or Month

GROUPBY can group dates without a helper column. Wrap the date column in a function inside the row_fields argument.

Total sales per year:

=GROUPBY(YEAR(A2:A24), D2:D24, SUM)

Total sales per year and month:

=GROUPBY(TEXT(A2:A24, "yyyy-mm"), D2:D24, SUM)

To add the sales manager next to the month, combine both columns with HSTACK:

=GROUPBY(HSTACK(TEXT(A2:A24, "yyyy-mm"), C2:C24), D2:D24, SUM)

What Is the Difference Between GROUPBY and PIVOTBY?

GROUPBY groups data by rows only. Its output is a vertical summary table with one column of categories and one or more columns of aggregated values.

PIVOTBY groups by both rows and columns, producing a cross-tabulation layout similar to a Pivot Table. It adds a col_fields argument that defines column grouping:

=PIVOTBY(row_fields, col_fields, values, function)

For example, to see total sales by Division (rows) and Quarter (columns):

=PIVOTBY(C2:C24, YEAR(A2:A24), D2:D24, SUM)

Use GROUPBY when you want a flat summary list. Use PIVOTBY when you need a matrix or cross-tab layout. Both functions share the same optional arguments for headers, totals, sorting, and filtering.

Excel Version Requirements for the GROUPBY Function

The Excel GROUPBY Function is currently only available in Excel for Microsoft 365.

Frequently Asked Questions

Is the GROUPBY function better than a Pivot Table?

GROUPBY is better for dynamic reports that update automatically without a manual refresh. It lives directly in the grid and can be referenced by other formulas. However, Pivot Tables are still superior for complex interactive dashboards with slicers, calculated fields, and drill-down functionality.

How do I exclude specific rows in a GROUPBY formula?

Use the filter_array argument (the 7th argument) to define a logical test. For example, a test with LEFT on column A can exclude every row that starts with the word Total. Any expression that produces a TRUE/FALSE array matching the length of your data works.

Which Excel versions support the GROUPBY function?

GROUPBY is available only in Microsoft 365 (Windows, Mac, and Excel for the web). It is not available in Excel 2021, Excel 2024, or any one-time purchase version.

Can GROUPBY aggregate text values instead of numbers?

Yes. Use ARRAYTOTEXT as the function argument to join all text values in each group into one comma-separated string. To list each value only once, use LAMBDA(x, ARRAYTOTEXT(UNIQUE(x))) as the function. For a custom delimiter, use a LAMBDA with TEXTJOIN.

What is the difference between GROUPBY and PIVOTBY?

GROUPBY groups data by rows only. PIVOTBY groups by both rows and columns, producing a cross-tabulation layout. Both use the same syntax for aggregation, headers, totals, and filtering. Use GROUPBY for flat summary lists. Use PIVOTBY for matrix-style reports.

How do I group dates by year or month with GROUPBY?

Wrap the date column in a function before you use it as the row field. YEAR gives yearly totals, and TEXT with the format yyyy-mm gives one row per year and month. To add another column next to it, combine both with HSTACK.

Download the Workbook

Get the Practice Workbook

The sample data and all GROUPBY and PIVOTBY examples in one file.

Leila Gharani

Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.