Ravindra BagaleCourses & study guides Track your progress

Excel PIVOTBY Function

By · Last updated:

Chala mitrano — today we learn PIVOTBY. Read Why → What → How, then try the practice list.

Version note. Microsoft 365 (newer builds). Companion to GROUPBY. Check your Version channel.
Fictional sample data for learning. Fictional region/category sales; verify PIVOTBY exists on your 365 build.

Purpose

Create a Pivot-like summary with row and column fields that spills as a dynamic array (Excel 365).

Return value

A spilled pivot-style grid (headers + aggregates).

Syntax

PIVOTBY(row_fields, col_fields, values, function, [options…])

Arguments

ArgumentRequired?Description
row_fieldsRequiredColumns that become row groups.
col_fieldsRequiredColumns that become column groups (can be empty handling depends on build).
valuesRequiredNumbers to aggregate.
functionRequiredAggregate such as SUM, AVERAGE.

Version

Microsoft 365 (newer builds). Companion to GROUPBY. Check your Version channel.

How to use — Why → What → How

Why: GROUPBY is great for row groups. PIVOTBY adds column fields like a classic PivotTable — without the Pivot cache UI.

What: One formula spills a matrix summary you can feed to charts.

How — numbered steps:

  1. Put data in a Table.
  2. Try a simple PIVOTBY with region rows and month columns.
  3. Compare to Insert → PivotTable for the same question.
  4. Watch #SPILL! room below and right.
  5. Prefer PivotTables when users must drag fields interactively.

Ravindra Bagale's Tip

PIVOTBY is formula-first; PivotTables are UI-first. Teach both.

Ravindra Bagale's Tip

If your Excel build lacks PIVOTBY, upgrade 365 or use PivotTable + GROUPBY.

Worked examples

1) Region × Category sales

Nashik sales table.

Formula: =PIVOTBY(RegionCol, CategoryCol, AmountCol, SUM)

Result: Matrix of sums

What it means: Rows=region, cols=category.

2) Average score by class and term

School sheet.

Formula: =PIVOTBY(ClassCol, TermCol, ScoreCol, AVERAGE)

Result: Average matrix

What it means: AVERAGE aggregate.

3) Count of orders

Use COUNTA/COUNT via supported function list.

Formula: =PIVOTBY(CityCol, StatusCol, OrderIdCol, COUNTA)

Result: Counts

What it means: Verify function support on your build.

4) UK cafe £ by daypart × item

Leeds.

Formula: =PIVOTBY(Daypart, Item, Sales, SUM)

Result: £ matrix

What it means: Same pattern, pounds.

5) Side-by-side with GROUPBY

Compare layouts.

Formula: GROUPBY for rows-only; PIVOTBY when you need columns.

Result: Two views

What it means: Choose the shape your manager wants.

Common errors

#NAME?: Build too old — function not recognised.
#SPILL!: Need a clear block for the matrix.
#VALUE!: Misaligned field ranges.

Practice tasks (10+)

  1. Task 1. Build a tiny PIVOTBY on 12 sample rows.
    Hint: try on a tiny range first
  2. Task 2. Compare to a PivotTable on the same data.
    Hint: try on a tiny range first
  3. Task 3. Change aggregate SUM → AVERAGE.
    Hint: try on a tiny range first
  4. Task 4. Explain #NAME? on older Excel.
    Hint: try on a tiny range first
  5. Task 5. Chart a PIVOTBY output.
    Hint: try on a tiny range first
  6. Task 6. Interview: PIVOTBY vs GROUPBY.
    Hint: try on a tiny range first
  7. Task 7. Ensure ranges are same height.
    Hint: try on a tiny range first
  8. Task 8. Document your Excel version in the file.
    Hint: try on a tiny range first
  9. Task 9. Try empty column field behaviour on your build.
    Hint: try on a tiny range first
  10. Task 10. Bonus: wrap with ROUND for display.
    Hint: try on a tiny range first

Quick quiz

Q1. PIVOTBY can place fields on columns as well as rows.
Q2. PIVOTBY is in every Excel 2016 install.
Q3. #NAME? may mean your build is too old.
Q4. GROUPBY is the closest companion function.

Answers: Q1=True, Q2=False, Q3=True, Q4=True

Related: LAMBDA · LET · GROUPBY · Function hub · Learning path