PERCENTOF computes what fraction a subset is of a total — and pairs perfectly with GROUPBY/PIVOTBY to add a “% of total” column to a grouped summary without extra math.
B2/SUM(B2:B10), but clearer inside grouped aggregations.
The example
Each region’s share of total sales.
| A | B | |
|---|---|---|
| 1 | Region | % of total |
| 2 | East | 32% |
| 3 | West | 29% |
The formula
Subset over total:
How it works
PERCENTOF expresses a part as a share:
- First argument: the subset (a value or range). Second: the total range.
- It returns subset-sum ÷ total-sum — the share of the whole.
- Its real power is as a GROUPBY/PIVOTBY aggregation: pass
PERCENTOFto get each group’s % of total automatically. - On its own it’s equivalent to
value / SUM(range), just more readable in array contexts.
Grouped % of total in one shot: =GROUPBY(Region, Sales, PERCENTOF) spills each region with its share of the grand total — the kind of thing that used to need a pivot’s “Show Values As.”
Try it: interactive demo
Values → each as % of total.
Variations
In GROUPBY
Grouped share of total:
Plain equivalent
Without PERCENTOF:
Subset of a subtotal
Share within a group total.
Pitfalls & errors
365 (2024+) only. PERCENTOF is recent; use value/SUM(range) elsewhere.
Zero total. A total of zero divides by zero — guard if the range can sum to 0.
Format as percent. The result is a ratio; apply a percentage format.
Practice workbook
Frequently asked questions
What does PERCENTOF do in Excel?
How do I add a % of total column to a grouped summary?
What's the equivalent without PERCENTOF?
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