How to Calculate Cumulative Percentage in Excel?

Imagine you bring home your test scores and you’re really happy with how you’ve scored…up until one of your parents asks you ‘what percentile is it?’ or ‘what’s the cumulative percentage of your score’?

That’s probably when you realize that you haven’t really scored as well as you thought!

Jokes aside, the cumulative percentage gives you one way to express or analyze a frequency distribution.

Using this, you can get a good idea of the percentage of values in the distribution that lie above or below a given value.

In this tutorial we will show you 3 ways to compute the cumulative percentage for different values (or intervals) in a given distribution:

  • By Manual Computation
  • Using a Formula
  • Using a Pivot Table

What is Cumulative Percentage and What is it Used For?

Simply put, the cumulative percentage of a value or interval is the percentage of the cumulative frequency for that value (or interval) in a given distribution.

It can also be considered as a sort of running total of a percentage value, where the percentage increases with each value.

The last value in the distribution (highest value) always has a cumulative percentage of 100%.

As such, it gives us a good idea of how the percentages in a frequency table add up over a time period.

For example, let’s say a dice was rolled 30 times and the following is the frequency distribution obtained:

Number and frequency dataset

If we know the cumulative percentage for each number in this distribution, we can tell how many times the dice rolled a number (say) between 1 and 4.

This can help us understand how probable it is to roll a number below 4.

So say after computing the cumulative percentage, this is the result we got:

Cumulative percentage for numbers

From the above result, we can tell that around 70% of the time the dice rolled a number below 4.

This can help us easily that there is a high probability of rolling a number below 4.

Similarly, you can use the cumulative percentage to find out what percentage of students scored below a given score.

If you find that the cumulative frequency of, say, 75 is 30%, it means that 30% of the students who gave the test scored below 75. You could also say that 70% of the students scored more than 75.

Of course, the applications of the cumulative percentage are not just limited to these two.

It is a widely used statistical analysis tool used in a number of industries, including scientific research finance, banking, and more.

Also read: How to Add a Percentage to a Number in Excel?

How to Calculate the Cumulative Percentage for a Frequency Distribution

As mentioned before, the cumulative percentage is basically a running total of percentage values.

So for each value, you have to find the frequency percentage of one period and add to it the frequency percentage of the previous periods.

Let’s see how the calculation is done manually. Let’s say you have the frequency distribution of student test scores as follows:

Score and frequency data

To find the cumulative percentage, you first need to compute the cumulative frequency of each score.

The cumulative frequency of each value is simply the total frequency of values including and below the said value, as shown below:

Calculating cumulative frequency

You can then compute the cumulative percentage of a value by dividing the cumulative frequency of the value by the total frequency, then multiplying by 100. 

Calculating cumulative percentage

So the cumulative percentage of , say, 50 is 18 / 46 * 100 = 39.1%.

Also read: How to Calculate Growth Rate in Excel

How to Calculate Cumulative Percentage in Excel

Of course, instead of manually computing the cumulative percentage, you can automate the process using Excel. There are 3 ways to get this done:

  • By Manual Computation
  • Using a Formula
  • Using a Pivot Table

Let us see each of these methods one by one. We will demonstrate each method using the same frequency distribution table:

Score and frequency dataset

Using Manual Computation to Calculate Cumulative Percentage in Excel

The first method uses simple manual computation to find the cumulative percentage.

We mentioned this method first, as it can help you clearly see how cumulative percentage computations are basically done.

Before finding the cumulative percentage in this method, we need to first compute the cumulative frequencies for each value.

So you will need to insert a helper column that will calculate the cumulative frequencies first.

Here are the steps:

  1. Create a helper column for Cumulative frequency.
Add a helper column
  1. Type the formula: =SUM($B$2:B2) in the first cell (C2) of this column. We locked the cell reference B2 into an absolute reference because when the formula is copied down to the other cells of the column, each time, we want to sum up values starting from cell B2 up to the current row.
SUM formula to get cumulative frequency
  1. Copy this formula down to the rest of the cells using the fill handle.
All cumulative frequency calculated
  1. You should now see column C populated with cumulative frequencies corresponding to each test score. The last value in this column (46) is the total frequency (in this case, the total number of students that were tested).
  2. Create a new column to compute the cumulative percentage.
Add another column for cumulative percentage
  1. Type the formula: =C2/$C$10 in cell D2. Here, we are dividing the cumulative frequency of the test score 25 by the total frequency. We locked the cell reference C10 with dollar symbols because we want this reference to remain the same when the formula is copied down to the rest of the cells in the column.
Formula to calculate cumulative percentage
  1. Copy this formula down to the rest of the cells using the fill handle.
Apply formula to the entire column
  1. You will notice that column D does not yet contain percentage values. To convert them to percentages, select all the values of the column (D2:D10). Then click on the Percent Style button (in the Number group of the Home tab).
Click on percentage icon to format the cells
  1. Column D should now show the cumulative percentage of all test score values. Notice that the last value returned is 100%, which means the computations were done correctly.
cumulative percentage in Excel

Using a Formula to Compute Cumulative Percentage in Excel

The above method can further be refined using a more compact formula. So, instead of adding a helper column, we could simply use the SUM functions to directly compute the cumulative percentage in one go.

The formula that you can use is as follows:

=SUM($B$2:B2)/SUM($B$2:$B$10)

Simply type this formula in the first cell of the column that will contain your cumulative percentages (cell C2) and copy it down to the rest of the cells using the fill handle.

After that, format the cells to display a percentage value as we did in step 9.

Here’s the result:

SUM formula to calculate cumulative percentage

You will notice this method gave us the same result but with a lesser number of steps.

Using a Pivot Table to Compute the Cumulative Percentage in Excel

The third method uses a Pivot table to quickly compute the cumulative percentage.

In addition to finding the cumulative percentage, the pivot table consists of a number of other data analysis tools that you can use.

To use this method to compute cumulative percentage you need to first create a Pivot table from your data. Here are the steps to do this:

  1. Select the range of cells containing your data (cells A1:B10 in our case)
  2. Navigate to Insert->Pivot Table.
Insert a pivot table
  1. This opens the ‘PivotTable from Table or Range’ dialog box.
‘PivotTable from Table or Range’ dialog box
  1. Select whether you want to open the PivotTable in a new or existing worksheet. We recommend selecting the former.
Select new worksheet
  1. Click OK.
  2. You should now see a blank PivotTable in a new sheet, with the PivotTable Fields sidebar to the right of the Excel window.
A blank pivot table is inserted
  1. Drag the Scores field to the Rows area as shown below:
Add score to the rows area
  1. Drag the Frequency field to the Values area as shown below:
Add frequency to the values area
  1. Your PivotTable should now be populated with the Scores column and a Sum of Frequency column.
Pivot table with sum of frequency

However, we don’t want to see the Sum of Frequency. We want to see the Cumulative Percentage of the Frequency

So, continue with the following steps:

  1. Double-click on the Sum of Frequency heading (cell B3).
  2. This should cause the Value Field Settings dialog box to appear.
Value field setting for frequency values
  1. Change the ‘Custom Name’ field value to ‘Cumulative Percentage’.
Change column name to cumulative percentage
  1. Select the Show Values As tab.
Select the Show Values As
  1. Click on the dropdown under ‘Show values as’.
Click on Show Values as drop down
  1. Scroll down and select ‘% Running Total In’ from the list of options.
Select % running total in option
  1. Click OK.

The second column of the PivotTable should now get replaced with the Cumulative Percentage for each score value.

Cumulative percentage values

Notice that these values are the same as the results we got using the previous 2 methods.

In this tutorial, we showed you 3 ways to calculate the cumulative percentage of distribution in Excel.

All 3 methods provide the same result, so you can pick the method that works best for you.

Other Excel tutorials you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment