Cross-Tabs with PIVOTBY

Excel Formulas › Dynamic Arrays

365 (2024+)PIVOTBY

PIVOTBY builds a two-way cross-tab in one formula — rows by one field, columns by another, values aggregated in the middle. A live PivotTable you never have to refresh.

Quick formula: region down, quarter across, sales in the cells:
=PIVOTBY(Region, Quarter, Sales, SUM)
Rows = unique regions, columns = unique quarters, each cell = summed sales for that combination.

Functions used (tap for the full reference guide):

PIVOTBYSUM

The example

Sales by region (rows) and quarter (columns).

ABC
1RegionQ1Q2
2East20,00022,000

The formula

Rows, columns, values, aggregation:

=PIVOTBY(Region, Quarter, Sales, SUM) // a live cross-tab

How it works

PIVOTBY lays out a full matrix:

  1. First argument: the row field. Second: the column field.
  2. Third: the values; fourth: the aggregation (SUM, AVERAGE, COUNT, LAMBDA).
  3. It spills a complete grid with row and column headers, plus optional totals.
  4. Like GROUPBY, it recalculates automatically when the data changes — no pivot refresh.

PIVOTBY vs PivotTable: PIVOTBY is live and formula-driven (great for dashboards that must always be current); a real PivotTable offers drag-and-drop, slicers, and drill-down. Use PIVOTBY for always-fresh summaries, a PivotTable for interactive exploration. Pre-2024, build a cross-tab with SUMIFS.

Try it: interactive demo

Live demo

Region x Quarter cross-tab (fixed sample).

Variations

Counts

Count instead of sum:

=PIVOTBY(Region, Quarter, Sales, COUNT)

Row totals only

Control totals with arguments.

SUMIFS cross-tab

Pre-2024:

=SUMIFS(Sales, Region, $A2, Quarter, B$1)

Pitfalls & errors

365 (2024+) only. Brand-new; fall back to a SUMIFS matrix where unavailable.

Spill room. The whole grid spills — keep the area below and right clear.

Not interactive. No slicers or drill-down — it’s a formula, not a PivotTable object.

Practice workbook

📊
Download the free Cross-Tabs with PIVOTBY practice workbook
PIVOTBY examples (formula text + result) with count and SUMIFS-cross-tab variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I make a cross-tab with a formula in Excel?
Use =PIVOTBY(rowField, columnField, values, SUM) to spill a live two-way summary. Requires Excel 365 (2024+).
How is PIVOTBY different from a PivotTable?
PIVOTBY is a live formula that recalculates automatically; a PivotTable is an interactive object with slicers and drill-down. Use PIVOTBY for always-fresh dashboards.
What's the pre-2024 equivalent?
Build a cross-tab with SUMIFS: =SUMIFS(values, rowField, $A2, colField, B$1) filled across a grid.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: GROUPBY · Two-way summary · GETPIVOTDATA

Function references: PIVOTBY · SUM