Excel PIVOTBY and GROUPBY Complete Guide: How to Create Pivot Tables with Formulas
PivotTables are powerful, but matching them to a report format can require many clicks and make them difficult to reuse. In contrast, when a summary table updates automatically with a single formula, duplicating files, creating monthly reports, and maintaining dashboards become much easier. This article covers practical patterns for Excel PIVOTBY and GROUPBY, including sorting, subtotals, percentages, filters, and performance.
Excel PIVOTBY and GROUPBY Quick Fix
- Convert the source data to a table: Click the range → Ctrl + T → select “My table has headers”
- Create a one-axis summary (vertical summary table) with GROUPBY
=GROUPBY(Sales[Product], Sales[Amount], SUM) - Create a two-axis summary (pivot layout) with PIVOTBY
=PIVOTBY(Sales[Product], Sales[Year], Sales[Amount], SUM) - Sort in descending order
=GROUPBY(Sales[Product], Sales[Amount], SUM,,, -2) - Apply a conditional filter
=GROUPBY(Sales[Product], Sales[Amount], SUM,,,, Sales[Region]="East")
GROUNDBY? The Actual Function Is GROUPBY (30-Second Clarification)
- Excel does not have a function named GROUNDBY.
- The latest dynamic-array summary functions are GROUPBY (one axis) and PIVOTBY (two axes).
What Are PIVOTBY and GROUPBY? (Their Relationship to PivotTables)
GROUPBY: Create a Summary Table Along “One Axis (Rows)”
GROUPBY groups data based on the specified row_fields and aggregates the corresponding values with functions such as SUM, AVERAGE, and COUNT.
PIVOTBY: Create a Summary Table Along “Two Axes (Rows + Columns)”
PIVOTBY groups data across two axes, row_fields and col_fields, to create results similar to a PivotTable. However, the official documentation states that although it can produce similar output, PIVOTBY is not directly related to Excel’s PivotTable feature.
Before You Start (Create a Table, Design Columns, and Understand Spill)
Why Create a Table (CTRL+T)?
- Formula ranges expand automatically when rows are added.
- Structured references such as Sales[Amount] are easier to maintain.
- Tables work well with summary formulas.
Understanding Spill Ranges
GROUPBY and PIVOTBY results spill across multiple cells. If the output area contains values, you will get a #SPILL! error, so clear the spill area or unmerge any merged cells.
Complete GROUPBY Guide
Official syntax:
GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])Three Required Arguments
- row_fields: The grouping criteria
- values: The values to aggregate
- function: The aggregation function (SUM, AVERAGE, COUNT, PERCENTOF, and so on)
field_headers
- Omitted: Automatic
- 0: No
- 1: Yes (do not display)
- 2: No (but generate)
- 3: Yes (display)
total_depth (Grand Totals/Subtotals)
- Omitted: Automatic (grand totals and subtotals where possible)
- 0: No totals
- 1: Grand total
- 2: Grand total + subtotals
- -1/-2: Display totals at the top
To use subtotals, row_fields must contain two or more columns.
sort_order (Sorting)
The sort index number corresponds to the row_fields and values columns. A negative number sorts in descending order.
filter_array (Conditional Filter)
This is a one-dimensional Boolean array indicating what to include or exclude, and it must be the same length as the source data.
field_relationship
- 0: Hierarchy (default)
- 1: Table (independent sorting; subtotals are not supported)
Complete PIVOTBY Guide
Official syntax:
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])Four Required Arguments
- row_fields: The row-axis groups
- col_fields: The column-axis groups
- values: The values to aggregate
- function: The aggregation function
row_total_depth / col_total_depth
These control whether grand totals and subtotals are displayed for rows and columns. To use subtotals, the fields on that axis must contain two or more columns.
relative_to (Denominator for Percentages/Ratios)
- 0: Column total (default)
- 1: Row total
- 2: Grand total
- 3: Parent column total
- 4: Parent row total
6 Practical Examples
Sample Data for the Examples (Copy and Paste)
Paste the table below into a worksheet, convert it to a table with Ctrl+T, and rename the table Sales.
| Date | Year | Month | Region | Product | Channel | Amount |
|---|---|---|---|---|---|---|
| 2025-01-05 | 2025 | 1 | East | A | Online | 120 |
| 2025-01-18 | 2025 | 1 | East | B | Offline | 80 |
| 2025-02-02 | 2025 | 2 | West | A | Online | 200 |
| 2025-02-10 | 2025 | 2 | West | C | Online | 90 |
| 2025-03-01 | 2025 | 3 | East | A | Offline | 150 |
| 2025-03-12 | 2025 | 3 | South | B | Online | 60 |
| 2024-11-03 | 2024 | 11 | East | A | Online | 70 |
| 2024-11-21 | 2024 | 11 | North | C | Offline | 110 |
| 2024-12-07 | 2024 | 12 | West | B | Online | 95 |
| 2024-12-19 | 2024 | 12 | South | A | Offline | 130 |
| 2024-12-28 | 2024 | 12 | East | C | Online | 40 |
| 2025-01-30 | 2025 | 1 | North | C | Offline | 55 |
Example 1) Total Sales by Year (GROUPBY)
=GROUPBY(Sales[Year], Sales[Amount], SUM)Example 2) Sales by Product, Sorted by Highest Sales First (Descending)
=GROUPBY(Sales[Product], Sales[Amount], SUM,,, -2)Example 3) Filter to East Region Data Only
=GROUPBY(Sales[Product], Sales[Amount], SUM,,,, Sales[Region]="East")Example 4) Product (Rows) × Year (Columns) Sales Matrix (PIVOTBY)
=PIVOTBY(Sales[Product], Sales[Year], Sales[Amount], SUM)Example 5) Percentage (%) (PERCENTOF + relative_to)
Percentage of the total (based on the grand total):
=PIVOTBY(Sales[Product], Sales[Year], Sales[Amount], PERCENTOF, , , , , , , 2)Percentage of each year’s column total (default):
=PIVOTBY(Sales[Product], Sales[Year], Sales[Amount], PERCENTOF, , , , , , , 0)Example 6) When You Want to See Both the Total and Count
=GROUPBY(Sales[Product], Sales[Amount], SUM)=GROUPBY(Sales[Product], Sales[Amount], COUNT)PivotTables vs. PIVOTBY
- PIVOTBY: Formula-based automatic updates and easy combination with other dynamic array functions
- PivotTables: Fast for UI-based analysis, formatting, PivotCharts, and slicers
Troubleshooting (Symptom | Cause | Solution)
| Symptom | Cause | Solution |
|---|---|---|
| #NAME? | Unsupported version or updates not installed | Check whether you have Microsoft 365 or a supported version; update Excel or use an alternative method |
| #SPILL! | Values or merged cells in the spill range | Clear the output range, unmerge cells, or move the output location |
| Unexpected sorting | Incorrect sort_order index | Recalculate using the fields + values column numbers (negative = descending) |
| Filter does not work | filter_array length mismatch | Make it the same length as the source data |
| Subtotals do not appear | Only one fields column | Subtotals require two or more fields |
| Unexpected percentages | Incorrect relative_to basis | Set it as intended: 0 (column), 1 (row), or 2 (grand total) |
Related Posts (Internal Links) & Next Steps
- Complete Guide to PivotTables, PivotCharts, and Slicers
- Excel Data Validation (Including Dynamic Lists and Spill Tips)
- Learn All Excel Functions for Free (A–Z)
- Complete Guide to the Excel XLOOKUP Function
- How to Use Excel Slicers (Advanced Filters)
Official Documentation (External Sources)
- Microsoft Support: GROUPBY Function
- Microsoft Support: PIVOTBY Function
- Microsoft Support: PERCENTOF function