📎 This article includes 1 downloadable practice file ↓
In this article
PERCENTOF returns a subset’s share of a total — mostly used inside GROUPBY and PIVOTBY to show % of total.
Syntax
=PERCENTOF(data_subset, data_all)| Argument | What it means |
|---|---|
data_all |
The full set. |
Examples
Example 1
=GROUPBY(B2:B500, E2:E500, PERCENTOF)Each region as % of total sales.
PERCENTOF in practice
Where you will use it
- Share of each region in total sales
- Category share of spend
- Contribution percentages in GROUPBY/PIVOTBY
Worked example
| Region | Sales | Formula | Result |
|---|---|---|---|
| North | 40000 | =PERCENTOF(B2,$B$2:$B$4) | 0.4 |
| South | 35000 | =PERCENTOF(B3,$B$2:$B$4) | 0.35 |
| West | 25000 | =PERCENTOF(B4,$B$2:$B$4) | 0.25 |
Shown as fractions; format as % in Excel. PERCENTOF needs the latest Microsoft 365, so these are B/SUM(B) worked out by hand.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Older Excel | PERCENTOF is new in Microsoft 365; use B2/SUM($B$2:$B$4) elsewhere |
| Unformatted result | Format the cell as % |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
⚡ Interactive Practice Lab & Formula Tester:
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Math & rounding24 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong