TL;DR — GROUPBY and PIVOTBY share a set of optional arguments that do things a pivot table can't do in one move:
PERCENTOFgives percent-of-total in thefunctionslot (=GROUPBY(C2:C1000, D2:D1000, PERCENTOF)), a HSTACK of functions returns several metrics at once, aLAMBDAin that same slot builds a weighted average, andfilter_arrayaggregates only the rows you choose — all live. Master thefunctiongrammar and you stop reaching for helper columns.
=GROUPBY(C2:C1000, D2:D1000, PERCENTOF) ' each region's % of the grand total
=GROUPBY(C2:C1000, D2:D1000, HSTACK(SUM, AVERAGE, COUNT)) ' three metrics, side by side
=GROUPBY(C2:C1000, D2:D1000, LAMBDA(v, SUM(v)/COUNT(v))) ' a custom aggregation
=GROUPBY(C2:C1000, D2:D1000, SUM, , , , (E2:E1000="West")) ' only rows where region = West
The basic GROUPBY/PIVOTBY call — group, aggregate, spill — already replaces the everyday
pivot table. The optional arguments are where they pull ahead of it. Percent-of-total,
several aggregations in one grid, a weighted average, a pre-filter on the rows: each is a
built-in argument here and a right-click-and-configure chore (or an impossibility) in a
PivotTable. The key that unlocks all of it is understanding what the function argument
really accepts.
What you'll learn
- Percent-of-total in one word with
PERCENTOF— and its #1 trap - Several metrics at once by stacking functions
- A custom aggregation by dropping a
LAMBDAin the function slot - Filtering rows before aggregating with
filter_array - Reading the errors:
#CALC!,#FIELD!,#SPILL!each point at one argument
The function slot accepts more than SUM
The single idea behind every trick in this article: the function argument isn't limited to
the built-in names. It accepts any eta-reduced function — a bare name like SUM, a
stack of names, a purpose-built helper like PERCENTOF, or a full LAMBDA you write. Once
you see that slot as "any function that turns a column into a summary," the advanced features
stop being separate features and become one flexible argument.
=GROUPBY(rows, values, SUM) ' a built-in name
=GROUPBY(rows, values, PERCENTOF) ' a purpose-built helper
=GROUPBY(rows, values, HSTACK(SUM, AVERAGE)) ' several names at once
=GROUPBY(rows, values, LAMBDA(v, MAX(v)-MIN(v))) ' your own function
Percent of total with PERCENTOF
The most-requested report — "what share of the total is each group?" — is a single word.
PERCENTOF sums each group and divides by the sum of everything:
=GROUPBY(C2:C1000, D2:D1000, PERCENTOF) ' Region | % of grand total (each row, e.g. 0.32)
The trap: the result is a fraction, not a formatted percent. 0.32 is thirty-two
percent, but the cell shows 0.32 until you apply Percent number formatting (Ctrl+Shift+%)
to the spill. This catches everyone once — the number is right, the display isn't, and
people assume the formula is broken. Format the spill range as Percent and it reads 32%.
Want share and total together? Stack them: HSTACK(SUM, PERCENTOF) gives the amount and its
percentage in adjacent columns.
Several metrics at once by stacking functions
You don't run GROUPBY three times for sum, average, and count — you stack the functions
with HSTACK and get three result columns from one formula:
=GROUPBY(C2:C1000, D2:D1000, HSTACK(SUM, AVERAGE, COUNT))
' Region | total sales | average sale | number of orders
Each function in the stack becomes a column, in order. This is the report a pivot table makes
you build by dragging the same field into Values three times and changing each one's
"Summarize by"; here it's one expression, and it reads like the header row you want:
SUM, AVERAGE, COUNT. Note the difference from widening values: more value columns
aggregate different data the same way; more functions aggregate the same data
different ways. Combine both for a full metrics grid.
A custom aggregation: LAMBDA in the function slot
When no built-in fits — a weighted average, a range, a trimmed figure — write a
LAMBDA. It receives each group's values as an array v, and
whatever it returns becomes the aggregate:
=GROUPBY(C2:C1000, D2:D1000, LAMBDA(v, MAX(v) - MIN(v))) ' the spread within each group
=GROUPBY(C2:C1000, D2:D1000, LAMBDA(v, TEXTJOIN(", ", 1, v))) ' list the members, don't total them
=GROUPBY(C2:C1000, D2:D1000, LAMBDA(v, SUMPRODUCT(v, w)/SUM(w))) ' a weighted average (w = weights)
This is the ceiling a pivot table simply can't reach: its "Summarize Values By" menu is a
fixed list, while the LAMBDA slot runs any logic you can express. The rule to remember —
your LAMBDA gets the group's values as one argument and must return a single value per
group; return an array and you'll get #CALC!. Weighted averages, medians of a filtered
subset, "top value as text" — all live in this slot.
Filter the rows before aggregating
filter_array (the seventh argument of GROUPBY) takes a boolean array the same height as
your data and aggregates only the TRUE rows — no helper column, no pre-filtering step:
=GROUPBY(C2:C1000, D2:D1000, SUM, , , , (E2:E1000 = "West")) ' only West-region rows
=GROUPBY(C2:C1000, D2:D1000, SUM, , , , (YEAR(A2:A1000) = 2026)) ' only 2026 rows
=GROUPBY(C2:C1000, D2:D1000, SUM, , , , (D2:D1000 >= 1000)) ' only orders >= 1000
The condition is written like a FILTER test — a comparison that
produces TRUE/FALSE down the column. The one rule that prevents the error: the
filter_array must be exactly as tall as row_fields and values. Point it at
E2:E1000 when your data is E2:E999 and you get #VALUE!; keep every range the same height
and it just works. This is how you build "this year only," "excluding refunds," or "West and
East only" summaries without touching the source data.
Reading the errors
The three errors these functions throw each point at a specific mistake — learn the map and debugging is instant:
#CALC! ' the function slot is wrong: SUM() instead of SUM, or a LAMBDA returning an array
#FIELD! ' a field/column reference is off — often field_headers set wrong, so headers leak in
#SPILL! ' the result is blocked — clear the cells below and to the right of the formula
#VALUE! ' usually filter_array is a different height than the data
#CALC! is almost always the function argument — a called SUM(), or a LAMBDA that
returns more than one value per group. #FIELD! points at the data shape, frequently the
field_headers argument: if your ranges include the header cells, set field_headers to 1
so the labels aren't grouped as data. #SPILL! is spatial, never logical — the formula is
fine, the landing zone isn't. And #VALUE! on a filtered aggregation is the height-mismatch
above. Each error is a signpost to one argument, not a mystery.
The judgment call
The moment a summary needs percent-of-total, more than one metric, a custom calculation, or
a subset of rows, these optional arguments beat a pivot table outright — each is one
argument here and a menu-dive or a dead end there. Keep three rules in hand: the function
slot takes a bare name, a stacked set of names, or a LAMBDA that returns one value per
group; PERCENTOF returns a fraction you must format as percent; and filter_array
must be the same height as the data. Read the errors as a map — #CALC! means the
function, #FIELD! means the headers, #SPILL! means the space, #VALUE! means the filter
height — and the last excuse for keeping a pivot table around (that it does the fancy stuff)
mostly falls away.
How ExcelMaster helps
The advanced arguments are powerful and unforgiving — one array-returning LAMBDA and the
whole thing is #CALC!. Tell ExcelMaster "each region's sales, its share of the total,
and the order count" and it writes =GROUPBY(C2:C1000, D2:D1000, HSTACK(SUM, PERCENTOF, COUNT)) and formats the percent column for you. Ask for "a weighted average price by
category, weighting by quantity" and it builds the LAMBDA. And when a formula returns
#CALC! or #VALUE!, it reads the error the way this guide does — pointing at the exact
argument that's wrong instead of leaving you to count commas.
Frequently asked questions
How do I show percent of total in GROUPBY or PIVOTBY?
Use PERCENTOF in the function slot: =GROUPBY(C2:C1000, D2:D1000, PERCENTOF). It divides
each group's sum by the grand total. The result is a fraction like 0.32, so apply Percent
formatting (Ctrl+Shift+%) to the spill to see 32%. To show the amount and its share side
by side, use HSTACK(SUM, PERCENTOF).
Can GROUPBY return more than one aggregation at once?
Yes — stack the functions: =GROUPBY(C2:C1000, D2:D1000, HSTACK(SUM, AVERAGE, COUNT)) returns
three columns. Each function becomes one result column, in order. That's different from
widening the values range, which aggregates different columns the same way; stacking
functions aggregates the same column several ways.
How do I use a custom formula as the aggregation?
Put a LAMBDA in the function slot. It receives each group's values as an array and must
return a single value: =GROUPBY(C2:C1000, D2:D1000, LAMBDA(v, MAX(v)-MIN(v))). This covers
weighted averages, ranges, and anything the built-in "Summarize by" list can't. If the
LAMBDA returns an array instead of one value, you'll get #CALC!.
How do I filter which rows GROUPBY aggregates?
Use the filter_array argument (the seventh): =GROUPBY(C2:C1000, D2:D1000, SUM, , , , (A2:A1000="West")) aggregates only the matching rows. The condition is written like a
FILTER test, and the boolean array must be the same height as
your data — a mismatch returns #VALUE!.
Why does my GROUPBY return #CALC! or #FIELD!?
#CALC! almost always means the function argument is wrong: SUM() instead of SUM, or a
LAMBDA that returns an array rather than one value per group. #FIELD! usually means the
field_headers argument is off — set it to 1 when your ranges include header cells so the
labels aren't treated as data. #SPILL! is unrelated to logic; it means cells below or to the
right are blocking the result.
Tested in
Tested in: Excel 365 (Windows 11) — last verified 2026-07-18.
Related guides: Excel GROUPBY · Excel PIVOTBY · Excel LAMBDA · LAMBDA Helpers · Excel FILTER