Build the running share that powers a Pareto chart — “the top 3 items are 80% of the total.” A running total divided by the grand total gives the cumulative percentage.
The example
Sorted values build to 100%.
| A | B | C | |
|---|---|---|---|
| 1 | Item | Value | Cum % |
| 2 | A | 50 | 50% |
| 3 | B | 30 | 80% |
The formula
Running total over the grand total:
How it works
A running total, expressed as a percent:
- Sort the values descending so the biggest contributors come first (the Pareto order).
SUM($B$2:B2)is the running total — anchored start, expanding end.- Divide by
SUM($B$2:$B$100), the grand total, for the cumulative percentage. - Copy down; the last row reaches 100%. Read where it crosses 80% for the “vital few.”
Pareto chart: plot the values as columns and the cumulative % as a line on a secondary axis (see the combo-chart recipe). The 80/20 crossover point tells you which few items drive most of the total.
Try it: interactive demo
Values (auto-sorted descending).
Variations
Per-item percent
Individual share:
Cumulative count %
By position:
Flag the 80% cut
Vital few:
Pitfalls & errors
Anchor the start, not the end. SUM($B$2:B2) — absolute start, relative end — is what makes it cumulative.
Sort first. Pareto logic needs descending order; an unsorted column gives a meaningless curve.
Format as percent. The ratio shows as a decimal unless formatted.
Practice workbook
Frequently asked questions
How do I calculate cumulative percent of total in Excel?
How do I make a Pareto chart?
Why must I anchor only the start of the SUM?
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