Pivot tables have long been the standard tool for summarizing and analyzing data in Excel, and they work well for many tasks. But if you’ve ever found yourself clicking through multiple menus just to adjust a grouping or refresh your data, you might appreciate an easier approach. Excel’s GROUPBY and PIVOTBY functions offer a different way to aggregate and organize data—one that’s more flexible and transparent than the pivot table interface.
I use these functions to summarize data with formulas instead of dialog boxes. That makes it easier to see exactly what’s happening and adjust analysis on the fly. If you’re also comfortable with Excel formulas, GROUPBY and PIVOTBY can ease your workflow and give you more control over how your data is structured.
GROUPBY is the go-to function for simple data aggregation
It simplifies creating summary reports
The GROUPBY function does exactly what its name suggests. It groups rows of data based on one or more columns and calculates summary values for each group. I use it to display total sales by region or average revenue by product without setting up a pivot table.
Let’s say you have a sales dataset tracking transactions across different regions and products. With GROUPBY, you can switch from pivot tables, allowing your analysis to update itself and get your totals in a single formula.
It uses the following syntax:
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])
The parameters break down like this:
- row_fields: The column or columns you want to group by. This could be a single column, such as region, or multiple columns if you need nested grouping—for example, region and product together.
- values: The data you want to aggregate. This is typically a column of numbers you want to sum, average, or count.
- function: The calculation to perform on each group. Common options include SUM, AVERAGE, COUNT, MAX, and MIN. You can use any function that works with arrays.
The following are the optional parameters:
- field_headers: Set this to 1 to include column headers in the output, or 0 to exclude them. If you omit this, Excel includes headers by default.
- total_depth: This adds grand total rows to your results. Set it to 1 for a single grand total, 2 for subtotals and grand totals, and so on.
- sort_order: Controls how the groups are sorted. Use 1 for ascending, -1 for descending, or omit it to keep the original order from your data.
- filter_array: A TRUE/FALSE array that filters which rows to include before grouping. This comes in handy when you only want to aggregate a subset of your data.
- field_relationship: Controls how Excel interprets the relationship between multiple row_fields when grouping. Set to 0 (or omit) to treat each combination of values as a unique group. Set to 1 to create a hierarchical relationship where the second field is nested under the first.
In the sales spreadsheet, you’ll find transaction records with Region, Product, Sales Rep, Date, and Amount columns. To calculate total sales by region, you’d use:
=GROUPBY(A2:A50, E2:E50, SUM)
This groups all rows by the region (column A) and sums the corresponding amount values (column E). The result is a two-column table showing each region and its total sales—no pivot table required.
If you want to see sales broken down by both region and product, you can pass multiple columns to the row_fields parameter:
=GROUPBY(A2:B50, E2:E50, SUM)
It creates a nested grouping where each region is broken down by product, giving you a more detailed view of where your revenue is coming from.
The output updates automatically whenever your source data changes. You don’t need to refresh or recalculate manually—the formula always reflects your current data.
One advantage GROUPBY has over pivot tables is its ability to aggregate text, not just numbers. If you want to see all sales reps listed for each region as a comma-separated list, you can use TEXTJOIN as your function parameter.
For example, the following formula groups by region and concatenates all sales rep names in each group.
=GROUPBY(A2:A50, C2:C50, LAMBDA(x, TEXTJOIN(", ", TRUE, x)))
Pivot tables can’t do this—they’re limited to counting or showing individual text values, not combining them into a single output.
PIVOTBY creates a familiar pivot layout without the hassle
Yes, a classic two-way table with a single formula
PIVOTBY takes data aggregation a step further by creating a two-dimensional summary—rows and columns working together. The difference is that you’re writing a formula instead of clicking through dialog boxes.
This function is handy when you need to see how two categories intersect. For example, if you want to view sales by region across different products, PIVOTBY arranges everything in a grid format that’s easy to scan.
It has a long syntax with multiple optional parameters:
=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to])
The parameters work like this:
- row_fields: The column or columns that define your rows. This is what appears down the left side of your output table. You can use a single column or multiple columns for nested row groupings.
- col_fields: The column or columns that define your columns. These values appear across the top of your output table, creating the horizontal dimension of your summary.
- values: The data you’re aggregating. This is typically a numeric column that you want to sum, average, or count for each combination of row and column values.
- function: The calculation to apply to each intersection of row and column groups. Common choices include SUM, AVERAGE, COUNT, MAX, and MIN.
The following are optional parameters:
- field_headers: Set to 1 to include headers in the output or 0 to exclude them. If you omit this, Excel includes headers by default.
- row_total_depth: Controls whether to add row totals and subtotals. Set to 1 for grand totals, 2 for subtotals and grand totals, and so on.
- row_sort_order: Determines how the row groups are sorted in the output. Use 1 for ascending order, -1 for descending order, or omit it to maintain the original order from your source data.
- col_total_depth: Same as row_total_depth, but for column totals. This adds summary columns on the right of your output.
- col_sort_order: Controls the sorting of column groups in your output table. Use 1 for ascending, -1 for descending, or leave it out to preserve the original sequence.
- filter_array: A TRUE/FALSE array that determines which rows from your source data should be included in the pivot calculation.
- relative_to: Changes how totals and percentages are calculated when using certain aggregation functions. Set to 0 (or omit) for standard totals. Set to 1 to calculate values as a percentage of row totals, or 2 for percentages of column totals.
Using the sales data spreadsheet, suppose you want to see total sales for each combination of region and product. You’d write:
=PIVOTBY(A2:A50, B2:B50, E2:E50, SUM)
This group rows by region (column A), creates columns for each product (column B), and sums the amount values (column E) for each intersection. The output is a grid showing exactly how much each product sold in each region. And the formula updates automatically as your data changes.
These functions are dynamic, transparent, and easier to integrate
Both GROUPBY and PIVOTBY, being formulas, play nicely with the rest of your spreadsheet. You can reference their output in other calculations, wrap them inside IF statements, or combine them with FILTER to create conditional summaries.
Transparency is a bigger advantage than it might seem. When someone opens your spreadsheet, they can click on the cell and see precisely how the summary was built.
There are no hidden pivot table caches or field settings to decode, making collaboration easier and reducing the “how did you get this number?” questions. You can build dashboards that update on their own. That kind of workflow is more complicated to set up with pivot tables, which require manual refreshes.
