How to Calculate Cumulative Percentage in Excel

We need to calculate the cumulative percentage in Excel for various reasons. Cumulative percentage helps us understand how the percentages in a frequency table add up. In this article, we will discuss 6 easy methods to calculate the cumulative percentage in Excel.


📁 Download Excel File

Download the following practice workbook to realize the topic more clearly.


What Is Cumulative Percentage in Excel?

A percentage is a ratio that is demonstrated as a fraction of 100. The cumulative percentage, on the other hand, is simply the sum of the percentages in any given dataset. In cumulative percentages, a percentage from one entry is added to a percentage from another entry, reaching 100%.


Learn to Calculate Cumulative Percentage in Excel with 6 Easy Approaches

Here, we have a sample dataset that contains different items and sales of a clothing store for the month of October. We will calculate the cumulative percentage in Excel in 6 different methods to better understand which items were sold the most.


Approach

1. Using Conventional Method

Here, we will use the conventional manual method to calculate the cumulative percentage. Firstly, we have to calculate Cumulative Frequency and then we will calculate Cumulative Percentage.

⬇️⬇️ STEPS ⬇️⬇️
  
  • First of all, select cell D5.
  • Now, type the following formula and press Enter:
=C5

  • Then go to cell D6. Enter the following formula and press Enter:
=C6+D5

  • Then, to use the Fill Handle Tool, move your cursor to the bottom right edge of cell D6 and double-click.

  • You have successfully calculated the Cumulative Frequency.

how to calculate cumulative percentage in excel manually

  • Now, select cells E5 to E10. Go to the Excel Ribbon and click Home.
  • Next, click the drop-down menu in the Number Select Percentage.
  • Now the Cumulative Percentage column is ready to show the data in the Percentage format.

how to calculate cumulative percentage in excel using cumulative frequency

  • After that, select cell E5. Type in the following formula and press Enter:
=D5/$D$10

  • Just like the following picture, double-click on the bottom right corner of cell E5 and apply the Fill Handle Tool.

  • Voila! You now have the Cumulative Percentage.

how to calculate cumulative percentage in excel manually

📕 Read More: Calculate Percentage Change with Negative Numbers in Excel


Approach

2. Utilizing Pivot Table to Calculate Cumulative Percentage

Now, it is time for another method to calculate the Cumulative Percentage. This time we will create a Pivot Table.

⬇️⬇️ STEPS ⬇️⬇️
  
  • Go to the Insert tab and select Pivot Table.

  • Momentarily, a dialog box will appear.

  • Now, select the dataset from B4 to C10 and press New Worksheet. Then click OK.

  • In the New Worksheet, options for the Pivot Table will appear.

  • From the PivotTable Fields, select Sales and drag it to the Rows just like the screenshot below.
  • After, drag Sales to Values.

inserting pivot table to calculate cumulative percentage in excel

  • Consequently, you will get a table like the following photo.
  • Pivot Table will automatically show the Sum of Sales.

  • Now to get the Cumulative Percentage column, double-click on the Sum of Sales. A Value Field Settings dialog box will appear.

  • Now, set the Custom Name to Cumulative Percentage.
  • Click Show Value As and next, from the drop-down menu select % Running Total In.
  • After that, click OK.

  • Finally, you will get the Cumulative Percentage in the column.

applying pivot table to calculate cumulative percentage in excel

Note: Pivot Table will automatically sort your dataset from the least Sales to the highest Sales.

Approach

3. Applying the SUM Function To Calculate Cumulative Percentage

Next, we will discuss how to calculate Cumulative Percentage using the SUM Function.

⬇️⬇️ STEPS ⬇️⬇️
  
  • In the beginning, select cell D5 and write the following formula, and press Enter:
=SUM($C$5:C5)

using sum function to calculate cumulative percentage in excel

  • Later, use the Fill Handle Tool from the bottom right edge of cell D5.

  • We have the Cumulative Frequency just like the screenshot below.

calculating cumulative percentage in excel using sum function

  • After that, select cell E5 to Enter the following formula:
=D5/$D$10

inserting sum function to calculate cumulative percentage in excel

  • Similarly, use the Fill Handle Tool to find the Cumulative Percentage.

📕 Read More: Calculate Profit and Loss Percentage with Formula in Excel



Approach

4. Finding Cumulative Percentage Using Running Total Method

There is another way to calculate Cumulative Percentage. This time we will calculate the Running Total along with the Percentage and Cumulative Percentage.

⬇️⬇️ STEPS ⬇️⬇️
  
  • At first, select cell C11 and type in the following formula to get the Running Total:
=SUM(C5:C10)

  • Now, select cell D5 to Enter the formula below:
=C5/$C$11

  • Later, use the Fill Handle Tool to apply the formulas automatically.

  • Now, we have got the Percentage.

utilizing running total to calculate cumulative percentage in excel

  • Next, to calculate Cumulative Percentage, select cell E6 and Enter the formula below:
=D5
  • After that, write the following formula for cell E6:

=D6+E5

how to calculate cumulative percentage in excel using running total method

  • Use the Fill Handle Tool from the bottom right corner of cell E6. Now, we have calculated the Cumulative Percentage.

applying running total to calculate cumulative percentage in excel

📕 Read More: Calculate Percentage in Excel Using Absolute Cell Reference


Approach

5. Inserting Direct Formula To Calculate Cumulative Percentage

Now we will show the direct formula to calculate the Cumulative Percentage.

⬇️⬇️ STEPS ⬇️⬇️
  
  • Select cell D5 and apply the formula:
=SUM($C$5:C5)/SUM($C$5:$C$10)

  • Use the Fill Handle Tool from the bottom right corner of cell D5 to calculate the Cumulative Percentage.

use of direct formula to calculate cumulative percentage in excel

📕 Read More: Calculate Discount Percentage Using Formula in Excel


Approach

6. Applying Data Analysis Tool to Calculate Cumulative Percentage

This is our final method to calculate the Cumulative Percentage. We will use the dataset below for this method.

Notice that we have introduced a new column Bin Range. Bin Range is a custom range that divides the entire dataset into a series of intervals. Here, we have divided equally between 0 to 130. We just rounded up the highest Sales (124) to 130 to demonstrate the dataset in a Histogram. Now let’s see how we calculate the Cumulative Percentage using the Data Analysis Tool.

⬇️⬇️ STEPS ⬇️⬇️
  
  • If you do not have the Data Analysis Tool under the Data Tab, go to the File tab.

  • Select Options from the bottom left corner.

  • An Excel Options dialog box will appear.
  • Then, go to the Add-ins options.
  • From the Manage drop-down menu select Excel Add-ins.
  • Click Analysis ToolPak from a bunch of Add-ins. And then click OK.

adding analysis toolpak from add-ins option to calculate cumulative percentage in excel

  • Then, you will find the Data Analysis Tool under the Data tab.
  • After that, click Data Analysis.

  • A Data Analysis dialog box will come.
  • Then, select Histogram and click OK.

  • A Histogram box will appear momentarily.
  • Then, select cells C5 to C10 for Input Range. For Bin Range select cells D5 to D10
  • Now, select cell F4 for Output Range.
  • Later, check out the Cumulative Percentage and Chart Output option.
  • Finally, click OK.

utilizing data analysis toolpak to calculate cumulative percentage in excel

  • Eventually, a Histogram, as well as a chart of Bin, Frequency, and Cumulative %, will appear.

inserting histogram and data analysis toolpak to calculate cumulative percentage in excel

  • Finally, the Histogram is shown in the following photo.

Here, the output is different from the other Cumulative Percentage because this method calculates the Cumulative Percentage only based on Frequency. Notice that we have only two item sales between 0 to 26 (Shirt and T shirt). Excel calculates that we have two Items out of six total Items (2/6 = 0.3333) and convert that into a percentage value on the Cumulative Percentage column. Therefore, Excel shows a Cumulative Percentage of 26, 33.33%.

Note: If you do not want More on the Histogram, you can simply delete the More row under the Output, and it will disappear from the Histogram.

📕 Read More: 4 Easy Ways to Calculate Percentage in Excel Based on Cell Color


📝 Takeaways from This Article

📌  First, we learned how to manually calculate the cumulative percentage using the cumulative frequency table.

📌  Then, we talked about inserting a pivot table to calculate the cumulative percentage.

📌  Using the SUM function formula to compute the cumulative percentage

📌  After that, we apply the running total to find the cumulative percentage

📌  Later, the cumulative percentage was easily calculated using a straightforward calculation.

📌  Finally, we analyzed the data graphically through a Histogram using the Data Analysis Tool


Conclusion

That concludes the discussion for today. These are some convenient methods to calculate the cumulative percentage. If you have any queries or recommendations, kindly let us know in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.


Related Articles

(Visited 65 times, 1 visits today)
Nazmus Sakib

Nazmus Sakib

Hello, and thanks for visiting my profile. Right now I am researching Microsoft Excel, thus I will be sharing articles about myriad ways to utilize Excel effectively here. I love to watch movies and TV series in my free time. I am also a huge football fan. My priority is to continually broaden my horizons in order to enrich my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo