When analyzing data, it’s important not only to sum the values, but to understand what share each row takes in the total on a running basis. This shows the contribution of each element and is used in sales analysis, ABC analysis and Pareto charts.
Cumulative share example: revenue by product
The source table:
| Product | Revenue |
|---|---|
| A | 400 |
| B | 300 |
| C | 200 |
| D | 100 |
The total is 1,000. The running total adds each value to the sum of the previous ones, and the cumulative share divides it by the total:
| Product | Revenue | Running total | Cumulative share |
|---|---|---|---|
| A | 400 | 400 | 40% |
| B | 300 | 700 | 70% |
| C | 200 | 900 | 90% |
| D | 100 | 1000 | 100% |
How to calculate the cumulative share with formulas
With the revenue in B2:B5, the running total in C2, filled down:
=SUM($B$2:B2)
Only the start of the range is fixed with dollar signs, so in each next row it includes one more value. The cumulative share in D2:
=SUM($B$2:B2)/SUM($B$2:$B$5)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Revenue | Running total | Cum. share |
| 2 | Laptops | 500 | 500=SUM($B$2:B2) | 50%=SUM($B$2:B2)/SUM($B$2:$B$5) |
| 3 | Phones | 300 | 800=SUM($B$2:B3) | 80%=SUM($B$2:B3)/SUM($B$2:$B$5) |
| 4 | Tablets | 150 | 950=SUM($B$2:B4) | 95%=SUM($B$2:B4)/SUM($B$2:$B$5) |
| 5 | Cables | 50 | 1000=SUM($B$2:B5) | 100%=SUM($B$2:B5)/SUM($B$2:$B$5) |
Only $B$2 is fixed, so the range grows by one row as the formula goes down. Laptops alone bring 50%, Laptops and Phones together 80%.
Format the result as a percentage. In Excel 365, the whole column can be calculated with one formula:
=SCAN(0,B2:B5,LAMBDA(acc,x,acc+x))/SUM(B2:B5)
| A | B | C | |
|---|---|---|---|
| 1 | Product | Revenue | SCAN share |
| 2 | Laptops | 500 | 50%=SCAN(0,B2:B5,LAMBDA(acc,x,acc+x))/SUM(B2:B5) |
| 3 | Phones | 300 | 80% |
| 4 | Tablets | 150 | 95% |
| 5 | Cables | 50 | 100% |
SCAN walks down B2:B5 and keeps the running total in acc; dividing by SUM turns it into shares. The result spills into all four rows.
Usually you should sort the values in descending order first, otherwise the result is hard to interpret. If you don’t want to sort, this formula gives each row the cumulative share it would have in a sorted list:
=SUMIF($B$2:$B$5,">="&B2)/SUM($B$2:$B$5)
| A | B | C | |
|---|---|---|---|
| 1 | Product | Revenue | Cum. share (no sorting) |
| 2 | Cables | 50 | 100.0%=SUMIF($B$2:$B$6,">="&B2)/SUM($B$2:$B$6) |
| 3 | Laptops | 500 | 43.5%=SUMIF($B$2:$B$6,">="&B3)/SUM($B$2:$B$6) |
| 4 | Tablets | 150 | 95.7%=SUMIF($B$2:$B$6,">="&B4)/SUM($B$2:$B$6) |
| 5 | Phones | 300 | 69.6%=SUMIF($B$2:$B$6,">="&B5)/SUM($B$2:$B$6) |
| 6 | Mice | 150 | 95.7%=SUMIF($B$2:$B$6,">="&B6)/SUM($B$2:$B$6) |
Each row gets the share it would have in a list sorted in descending order. Tablets and Mice both have 150 and get the same share (difference from SUM with a growing range).
It adds all values greater than or equal to the current one. Equal values get the same share.
Calculate the cumulative share with !SEMTools
With the !SEMTools add-in, you get the cumulative share at once, without the intermediate running total:
- select the range of values;
- go to the !SEMTools tab;
- open the Extract → Count menu;
- click Accumulated share.
If the “output to the next column” checkbox is on, the cumulative share appears to the right of the source column; otherwise, the values are replaced in place. The second option is handy too if you work on a copy of the data. The add-in returns values only, no formulas.
Where the cumulative share is used
- sales analysis: which products bring 80% of revenue;
- traffic analysis: which pages bring most of the visits;
- financial reports: distribution by clients, regions, categories;
- ABC analysis: finding the key elements by the Pareto principle.
Tips: sorting and Pareto charts
- sort the values in descending order before calculating, or use the SUMIF formula above;
- for a chart, a combo chart works best: columns for the values and a line for the cumulative share on a secondary axis;
- if you do this analysis often, automation saves a lot of time.
Get the cumulative share of a whole column in one click, in place or into the next column, without running-total formulas.
!SEMTools solves this and hundreds of other tasks right in Excel!
Summary: running percentage in Excel
I hope you found what you were looking for on this page. The cumulative share is the running total divided by the total: SUM with a growing range in any Excel, SCAN in Excel 365, or one click in !SEMTools. Other metrics are in the statistical metrics section.