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.
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.
- 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.
- 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.
- 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.
📕 Read More: Calculate Percentage Change with Negative Numbers in Excel
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.
- 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.
- 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 C column.
3. Applying the SUM Function To Calculate Cumulative Percentage
Next, we will discuss how to calculate Cumulative Percentage using the SUM Function.
- In the beginning, select cell D5 and write the following formula, and press Enter:
=SUM($C$5:C5)
- Later, use the Fill Handle Tool from the bottom right edge of cell D5.
- We have the Cumulative Frequency just like the screenshot below.
- After that, select cell E5 to Enter the following formula:
=D5/$D$10
- Similarly, use the Fill Handle Tool to find the Cumulative Percentage.
📕 Read More: Calculate Profit and Loss Percentage with Formula in Excel
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.
- 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.
- 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
- Use the Fill Handle Tool from the bottom right corner of cell E6. Now, we have calculated the Cumulative Percentage.
📕 Read More: Calculate Percentage in Excel Using Absolute Cell Reference
5. Inserting Direct Formula To Calculate Cumulative Percentage
Now we will show the direct formula to calculate the Cumulative Percentage.
- 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.
📕 Read More: Calculate Discount Percentage Using Formula in Excel
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.
- 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.
- 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.
- Eventually, a Histogram, as well as a chart of Bin, Frequency, and Cumulative %, will appear.
- 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%.
📕 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
- 2 Examples to Convert Percentage to Basis Points in Excel
- 3 Ways to Add Percentage to Price in Excel with Formula
- 2 Ways to Calculate Bacterial Growth Rate in Excel
- 3 Easy Ways to Calculate Accuracy Percentage in Excel
- 4 Simple Ways to Calculate Forecast Accuracy Percentage in Excel
- 6 Ways to Forecast Growth Rate in Excel
- Calculate Percentage in Excel Based on Conditional Formatting