Guides

PivotTable vs GROUPBY and PIVOTBY: Which Should You Use in Excel?

By Ravindra Bagale, Excel & Data Analytics trainer · LinkedIn · Published: 7 October 2026 · Last updated: 7 October 2026

Salman needs sales by city every Monday for Raja Coffee Roasters. Last week he made a PivotTable; this week he added new orders and forgot to press Refresh, so the report was out of date. In Microsoft 365, one formula, =GROUPBY(Orders[City],Orders[Amount],SUM), gives the same table (Pune ₹10,700, total ₹31,540) and updates by itself. A PivotTable still wins for slicers, drill-down and quick dragging of fields.

A PivotTable is a drag-and-drop report that you refresh, with slicers, drill-down, Show Values As and the Data Model. GROUPBY (one axis) and PIVOTBY (rows and columns) are Excel for Microsoft 365 functions that build the same kind of summary as a formula that recalculates automatically and can feed other formulas. Use a PivotTable for exploring and for interactive reports; use GROUPBY or PIVOTBY for summaries that must stay current and sit inside a formula-driven model. In older versions, use SUMIFS by category.

Download the practice file (pivottable-vs-groupby-pivotby.xlsx)

Excel file, 10 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

The same summary three ways

PivotTable:  Rows = City, Values = Sum of Amount
GROUPBY:     =GROUPBY(Orders[City],Orders[Amount],SUM)
SUMIFS:      =SUMIFS(Orders[Amount],Orders[City],A4)   (one row per city)

We verified the SUMIFS numbers by recalculating the practice file. GROUPBY and PIVOTBY are not available in every Excel version, so the practice file keeps them as text on their own sheet, written to the syntax on Microsoft's function pages.

GROUPBY and PIVOTBY formulas

1. The first two arguments are what to group by and what to add up; the third is the function (SUM, AVERAGE, COUNT, PERCENTOF and others).
2. Leave arguments empty with commas to skip them: in SUM,,,-2 the -2 is sort_order, meaning sort by the second column (the totals), largest first.
3. filter_array is a TRUE/FALSE test the same length as the data, such as Orders[Qty]>=3.
4. The result spills. Refer to the whole result with the spill operator, for example =F2#.

Syntax from Microsoft: GROUPBY function and PIVOTBY function. Microsoft's PIVOTBY page notes that, although it can produce similar outputs, PIVOTBY is not directly related to the PivotTable feature.

Differences that decide it

Which one to use

1. Exploring new data or building an interactive report with slicers: PivotTable.
2. A summary inside a model that must always be current (dashboards fed by formulas, monthly packs): GROUPBY or PIVOTBY.
3. Sharing with people on Excel 2019 or older: PivotTable or SUMIFS, because the functions will not calculate there.
4. Millions of rows or several related tables: PivotTable on the Data Model, or Power Pivot.