Cellwise Start free

Excel guide · 5 min read

How to calculate cumulative percentage in Excel

Quick answerDivide a running total by the grand total: in C2 type =SUM($B$2:B2)/SUM($B$2:$B$9), fill it down and format it as Percent. The first row shows that item's share, each row below adds the next one, and the last row reaches 100%. For a Pareto analysis, sort the values largest first before you add the column.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFG
1Reason (sorted)ComplaintsCumulative %Pareto groupReason (export order)Complaints
2Damaged packaging65Late delivery42
3Late delivery42Wrong item sent18
4Missing parts27Damaged packaging65
5Wrong item sent18Billing error9
6Website checkout bug12Rude support agent6
7Billing error9Missing parts27
8Rude support agent6Website checkout bug12
9Other5Other5

Download this sheet as an Excel practice file (.xlsx)

The formula: running total over a locked total

Column B counts one quarter's customer complaints by reason, 184 in all, sorted largest first. The cumulative percentage answers "how much of the problem do the top reasons cover?"

In SUM($B$2:B2) only the start is locked, so as you fill down the range grows to B2:B3, B2:B4 and so on. The total SUM($B$2:$B$9) is locked at both ends so it never moves. In row 5 the running total is 65+42+27+18 = 152. The column reads 35.3%, 58.2%, 72.8%, 82.6%, 89.1%, 94.0%, 97.3% and 100%.

=SUM($B$2:B5)/SUM($B$2:$B$9)

Result: 0.82608695652 shows as 82.6% with Percent format

The top four reasons, down to Wrong item sent, cover 152 of 184 complaints.

Sort largest first, or the numbers mean nothing

A cumulative percentage only tells you about "the top few" if the rows are in that order. In the export order in F:G, the third row is Damaged packaging and the running share there is 67.9%: a mix of the first three rows in whatever order the help desk listed them. It answers no useful question.

Sort with Data > Sort, by Complaints, Largest to Smallest, and keep My data has headers ticked. In Excel 365 and 2021 you can keep the export as it is and spill a sorted copy with =SORT(F2:G9,2,-1), then point the running SUM at the copy. Keep a catch-all like Other at the bottom even if it is large, since it is not one cause you can fix.

  1. Click any cell in the data.
  2. Go to Data > Sort.
  3. Sort by Complaints, Sort On Cell Values, Order Largest to Smallest, then click OK.
=SORT(F2:G9,2,-1)

Result: spills 8 rows × 2 columns

Damaged packaging65
Late delivery42
Missing parts27
Wrong item sent18
Website checkout bug12
Billing error9
Rude support agent6
Other5

The eight reasons sorted by complaints, from Damaged packaging (65) down to Other (5): the same order as column A.

Flag the vital few at 80%

The usual Pareto rule of thumb is that a few causes make up about 80% of the effect. To mark those rows, check the cumulative percentage of the rows above: if it was still under 80%, this row is needed to reach 80%. That way the row that crosses the line, Wrong item sent at 82.6%, is included.

The simpler test, cumulative % <= 80%, stops one row short and leaves you with three reasons covering only 72.8%. Neither is wrong, but pick one, write it next to the chart, and do not move the cut-off to suit the result. Put 0.8 in a cell if people will want to try 70% or 90%.

=IF(SUM($B$2:B5)-B5<0.8*SUM($B$2:$B$9),"Vital few","Useful many")

Result: Vital few

Vital few: the three reasons above Wrong item sent reach only 72.8%, so this row is needed to pass 80%. In row 6 the formula returns Useful many.

The two mistakes that break the column

If the total is not locked, filling down shifts it as well. In row 5, SUM(B2:B9) has become SUM(B5:B12), only 50 complaints, and the cumulative share jumps above 100%. A quick check catches both this and a total range that is too short: the last row must show exactly 100%.

The other mistake is adding share percentages that were rounded first. If you round each item's share to whole percent and then add them up, the last row can end at 99% or 101%. Calculate from the raw counts as shown here, and round only the display with number formatting.

=SUM($B$2:B5)/SUM(B5:B12)

Result: 3.04

3.04, or 304%: the total slid down to B5:B12 because it was not locked with $ signs.

Without sorting: SUMIF on the value

If you cannot sort the source, SUMIF can give each row the share of all items at least as large as itself. On the export in G, Wrong item sent (18) gets the sum of 65, 42, 27 and 18, divided by the total. That is the same 82.6% as the sorted list.

Ties are the catch. If two reasons both had 18 complaints, each would include the other and both would show the combined figure, so the column would skip a step. For a report with ties, sort and use the running SUM. In Excel 365 you can also spill the whole column at once with =SCAN(0,B2:B9,LAMBDA(a,v,a+v))/SUM(B2:B9) on the sorted list; the Cellwise practice engine cannot run SCAN, so try that one in Excel.

=SUMIF($G$2:$G$9,">="&G3)/SUM($G$2:$G$9)

Result: 0.82608695652 shows as 82.6% with Percent format

Wrong item sent's cumulative share, worked out without sorting: everything with 18 or more complaints over the 184 total.

Pareto chart and PivotTable, no formulas

Excel 2016 and later has a built-in Pareto chart. It sorts the bars largest first and draws the cumulative percentage line on a second axis by itself, so you only select the reasons and counts. It does not mark an 80% line, so keep the helper columns if you need the cut-off.

In a PivotTable, sort the counts largest to smallest, then show the values as % Running Total In. The running total follows the order of the rows on screen, which is why the sort comes first.

  1. Select A1:B9.
  2. Go to Insert > Insert Statistic Chart > Pareto.
  3. For a PivotTable: put Reason in Rows and Complaints in Values, then right-click a count > Sort > Sort Largest to Smallest.
  4. Right-click a count again > Show Values As > % Running Total In, choose Reason as the base field and click OK.

Common mistakes

Forgetting to sort before the running percentage

The numbers only describe "the top causes" when the rows go from largest to smallest. Sort first, or use SORT to make a sorted copy.

Total range not locked with $

The total shifts as you fill down and the column passes 100%. Use SUM($B$2:$B$9) and check that the last row is exactly 100%.

Adding up rounded percentages

Rounded shares can end at 99% or 101%. Build the running total from the raw counts and round only the display.

Locking both ends of the running range

SUM($B$2:$B$2) does not grow, so every row shows only the first item's share. Lock the start only: SUM($B$2:B2).

FAQ

What is the formula for cumulative percentage in Excel?

=SUM($B$2:B2)/SUM($B$2:$B$9) in the first row, filled down and formatted as Percent. The running range grows by one row each time while the total stays fixed.

How do I find which items make up 80%?

Sort largest first, add the cumulative percentage, then flag rows where the cumulative percentage of the rows above is under 80%. Those rows are the vital few, including the one that crosses 80%.

What is the difference between percentage of total and cumulative percentage?

Percentage of total is one item's share, like 9.8% for Wrong item sent. Cumulative percentage adds that share to every share above it, like 82.6% for the top four reasons together.

Why does my cumulative percentage not end at 100%?

The total range is either too short, unlocked, or the shares were rounded before they were added. Lock the total with $ around the full column of values and calculate from the raw numbers.

Does Excel have a Pareto chart?

Yes, in Excel 2016 and later: select the categories and values and go to Insert > Insert Statistic Chart > Pareto. In older versions build a combo chart from the counts and your cumulative percentage column.

Functions used

Related guides

Updated