GROUPBY and PIVOTBY vs Pivot Table

Excel’s GROUPBY and PIVOTBY functions provide a modern, formula‑based alternative to PivotTables using dynamic arrays.

The key distinction is that PIVOTBY extends the capabilities of GROUPBY:

  • GROUPBY groups data by rows only
  • PIVOTBY groups by both rows and columns

Mandatory GROUPBY Syntax:

= GROUPBY (row_fields, values, function)
  1. row_fields  – row(s) to list in rows
  2. values– row(s) to aggregate as report values
  3. function– to aggregate as report values

= GROUPBY (Table01[[#All],[Name]], Table01[[#All],[Sales]], SUM)

Optional Arguments of GROUPBY.

  • [field_headers]– displaying column labels
  • [total_depth]– subtotals and grand totals
  • [sort_order]– how to sort rows
  • [filter_array]– filter which data source rows flow into the report
  • [field_relationship] – Either Hierarchy (0) or Table (1)

Mandatory PIVOTBY Syntax:

= PIVOTBY (row_fields, col_fileds, values, function)
  1. row_fields – column(s) to list in rows
  2. col_fields – column(s) to list in columns
  3. values – column(s) to aggregate as report values
  4. function– aggregate function used to summarize values

= PIVOTBY (Table01[[#All],[Name]], Table01[[#All],[Region]], Table01[[#All],[Sales]], SUM)

Optional Arguments of PIVOTBY.

  • [field_headers] – displaying column labels
  • [row_total_depth] – subtotals and grand totals
  • [row_sort_order] – how to sort rows
  • [col_total_depth] – column totals and subtotals
  • [col_sort_order] – how to sort columns
  • [filter_array] – filter which data source rows flow into the report
  • [relative_to] – This is typically used when PERCENTOF is supplied to function.

Is the PivotTable dead after new functions release?

I don’t think so. Let’s take a closer look: Pivot Table vs. PIVOTBY ⚖️

Pivot Table – Strengths & Advantages

  • Extremely easy to create with a few clicks
  • Automatic date grouping (Years, Quarters, Months) + custom grouping
  • Slicers & Timelines for interactive filtering
  • Custom grouping of any field (e.g., ranges, categories)
  • PivotCharts with instant row/column switching
  • “Show Report Filter Pages” to generate multiple sheets automatically
  • Drag‑and‑drop field navigation — perfect for exploration
  • Fast sorting & filtering
  • Automatic formatting (subtotals, grand totals, number formats)
  • Does not require formulas — works from the Pivot Cache
  • Great for dashboards and non‑technical users
  • Refreshable from external data sources (Power Query, SQL)
  • Supports Calculated Fields (though limited compared to formulas)

PIVOTBY – Strengths & Advantages

  • Instant results (dynamic array spill), and PivotTables however will soon have this as well.
  • Works with text aggregation (something PivotTables struggle with)
  • Fully formula‑based – great for advanced modelling
  • Can be nested inside other formulas
  • Supports LAMBDA, BYROW, BYCOL, etc.
  • No Pivot Cache – always live, always recalculating
  • Spill range reference (=ref#) makes it easy to reuse results
  • More flexible calculations than PivotTable Calculated Fields
  • Better for automation — no refresh button needed
  • Great for building dynamic reports that update with formulas
  • Works well with FILTER, SORT, TAKE, DROP, WRAPROWS, WRAPCOLS.

The new functions GROUPBY and PIVOTBY are definitely more powerful than PivotTables. PivotTables remain perfect for ad‑hoc tasks or even for double‑checking results. Have I missed anything? I’d love to hear your thoughts. 📩