How to Calculate Cumulative Percentage in Excel (6 Methods)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel provides numerous methods & functions to calculate the cumulative percentage. Instead of determining these cumulative percentages manually for a huge range of data, you can do it within minutes with the help of Excel functions. In this article, we will demonstrate sis different methods to calculate cumulative percentages in Excel.


Watch Video – Calculate Cumulative Percentage in Excel


What Is Cumulative Percentage?

If you don’t know what exactly Cumulative Percentage is then here the definition goes for you-

“A running tally of the percentages found in a group of answers. After adding up all of the prior percentages, the sum will either stay the same or rise, reaching the highest amount of 100%.”

Source: https://dictionary.apa.org/cumulative-percentage


How to Calculate Cumulative Percentage in Excel: 6 Useful Methods

I’ve found the easiest & most effective 6 methods on this topic so far & you can use any of them after grabbing some fruitful knowledge by going through these techniques.


1. Manual Approach to Calculate Cumulative Frequency & Determining the Cumulative Frequency Percentage

Suppose, a business company started its journey in 2011. After 10 years of business, they want to know about their progress rate of product sales count with the help of running total(Cumulative Frequency) & running total percentage(Cumulative Percentage). So here’s our data below in the picture where you have to find Cumulative Frequency as well as Cumulative Percentage in two specified columns.

Calculate Cumulative Percentage in Excel

Steps:

  • Firstly, select Cell D5.
  • Secondly, tap on Cell C5.
  • Thirdly, press Enter.

You’ve just defined the starting point in Cell D5 to calculate cumulative frequency.

Calculate Cumulative Percentage in Excel

  • Now, go to cell D6.
  • Then, add C6 with D5. So, we need to write the formula.
=C6+D5
  • Next, press the Enter key.

Through this process, you’re adding the sales of 2012 & those from the previous year.

  • Use the Fill Handle to drag or fill down the cell to D14.

  • You’ll get the cumulative sales for all years at once.

Calculate Cumulative Percentage in Excel

  • Now select the whole Column E where you have to determine the cumulative percentages.
  • Under the Home ribbon or tab, select the Percentage option from the drop-down in the Number group of commands.
  • It’ll make sure the divided values in Column E will turn into percentages.

Calculate Cumulative Percentage in Excel

  • In cell E5, divide D5(1st value from cumulative frequency)  by D14(Total Sales). So, the formula will be.
=D5/$D$14
  • You have to lock cell D14 by pressing F4 after selecting cell D14 in the Function Bar.
  • Unless you lock this cell D14, cumulative percentages will show as errors later for the rest of the cells in column E.
  • If you need to be enlightened more about locking or changing Cell References then you can go here to find in details on this term.

Calculate Cumulative Percentage in Excel

  • Use the Fill Handle again to fill down cells E5 to E15.

  • You’ve just got the cumulative percentages for all sales year by year.

Calculate Cumulative Percentage in Excel

Read More: Make an Excel Spreadsheet Automatically Calculate Percentage


2. Apply Data Ranges or Intervals into Histogram

We can find cumulative percentages by using a Histogram too. Let’s do this by reusing the previous datasheet. Here, you have to add a set of ranges or intervals & the Histogram chart will show you the frequency percentages for these intervals. Let’s follow the procedures to calculate cumulative percentage in Excel.

Steps:

  • If you don’t have the Data Analysis command under the Data ribbon then you have to enable it first.
  • Go to the File tab from the ribbon.

Calculate Cumulative Percentage in Excel

  • Further, from the File tab, go to Options.

  • Now, select Add-ins.
  • Consequently, click on Analysis ToolPack, and you’ll find Excel Add-ins inside the Manage drop-down.
  • Finally, press OK.

  • Under the Data ribbon, now select the Data Analysis command from the Analysis group of commands.

  • Tap on the Histogram option & press OK.

  • Choose the Cell Range C5:C14 as the Input Range.
  • Inside the Bin Range, input the Range or Intervals.
  • Select E4 Cell as Output Range.
  • Mark on Cumulative Percentage & Chart Output.
  • Press OK.

  • You’ll find the Cumulative Percentages along with the Histogram Chart where you can customize the view too through multiple options.

Calculate Cumulative Percentage in Excel

NOTE: Through this method, you won’t exactly get the cumulative sales frequency or percentages year by year but this Histogram will show you the frequency of the sales ranges over those 10 years mentioned. You’ll be able to know which range of your sales counts most or least in that span of years.

3. Create an Excel Pivot Table to Determine Cumulative Percentage

If you opt to create a Pivot Table then it’ll be easier & time-saving to determine the Cumulative Percentage. Now we’ll create this Pivot Table for a similar datasheet mentioned above.

Steps:

  • Under the Home tab, select Analyze Data from the Analyze group of commands.
  • Thus, a side window will appear like the picture below.
  • Choose Insert Pivot Table.

  • You’ll find a new spreadsheet where you’ll have the Sum of Sales by default.
  • But you have to find a cumulative percentage now.

  • Double-click cell B3.
  • A toolbox named Value Field Settings will appear.
  • Select Show Value As the bar.

  • Now Type ‘Cumulative Percentage’ in place of ‘Sum of Sales’ inside the Custom Name box.
  • Under the Show Values As drop-down, select % Running Total In.
  • Press OK.

  • In Column B, the Cumulative Percentages will be shown. You’ve just transformed Unit Sales into Cumulative Percentages year by year.


4. Find out the Percentage of the Unit Values and running Total in Excel

Let’s find this cumulative percentage by applying another method now. We will use the SUM Function.

Steps:

  • To begin with, select cell C15.
  • Then, add all Sales values by typing the formula.
=SUM(C5:C14)
  • Press Enter & you’ll get the Total Sales as 1441 Units.

Calculate Cumulative Percentage in Excel

  • Now, select Columns D & E.
  • Under the Home tab, choose Percentage from the drop-down in the Number group of commands.

  • At this point, click on cell D5.
  • Divide C5 by C15, it’ll show the result as a sales percentage in the year 2011. So, type the formula.
=C5/$C$15
  • Make sure, you’ve locked the C15 cell by pressing F4 after typing C15 otherwise all other sales percentages will be shown as Value Error because the Sales values will be divided by empty cells consecutively under the C15 cell.

  • Drag or fill down cells D5 to D14 with the Fill Handle option.

  • Furthermore, go to cell E5 and insert the formula down.
  • Thus, the value from cell C5 will be copied.
  • Now select cell E5 & add D6 & E5 cells.

  • Fill down cells E7 to E14.

Calculate Cumulative Percentage in Excel

  • You’ll get all cumulative percentage values right away.


5. Use Sum Function to Compute Cumulative Frequency and Percentage

You can use the Sum function here too to calculate cumulative frequency first.

Steps:

  • Select cell D5 & type the formula down.
=SUM($C2$5:C5)
  • Further, press the Enter key.
  • By locking C5 cell 1st, it’ll make sure each of the next cells will be added to the just previous cell when you’ll go to find the cumulative frequency of all cells in Column D in the next step.

  • Now, use the Fill Handler in cell D5 to fill down D6:D14.
  • You’ve just got the cumulative frequencies of all sales year by year.

  • Select cell E5 & insert the simple formula below.
=D5/$D$14
  • This means you’re dividing D5 by Total Sales from D14.
  • You have to lock cell D14 as you’re dividing all Sales values from Column E by only D14 every time.
  • Don’t forget to enable the Percentage format for Column E by choosing from the drop-down in the Number group of commands.

  • Finally, you’ll get all cumulative percentage values.

Calculate Cumulative Percentage in Excel


6. Embed Immediate Formula to Calculate Cumulative Percentage in Excel

And now here’s the last method where we’ll use the direct formula. It’s actually what we’ve done in the last method by applying 2-step formulas, now we’ll do it by combining those formulas into a single one.

Steps:

  • Firstly, select cell D5 and type the formula there.
=SUM($C$5:C5)/SUM($C$5:$C$14)
  • After that, press Enter.
  • Inside the parenthesis & in the numerator part, you’re calculating the cumulative frequency of the sales values.
  • And in the denominator part, it’s the sum of all sales values in total and as the total value won’t change for any cell in Column D, so we have to make sure the cells are locked by using $ signs before both Column Names & Row Numbers.
  • Lastly, use the Fill Handle to drag down Cell D5 to D14 & the entire cumulative frequency will be displayed.

  • Lastly, you will get the cumulative percentage.

Calculate Cumulative Percentage in Excel


Download the Practice Workbook

You can download our practice worksheet below that we’ve used while preparing this article.


Conclusion

The above methods will assist you to Calculate Cumulative Percentage in Excel. I hope you’ve liked all of these basic methods mentioned to find out the cumulative percentages. If you have questions or thoughts regarding the methods in this article, then you’re always welcome to comment. I’ll catch up with your valuable words soon!


Related Articles


<<Go Back to Calculating Percentages in Excel | How to Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo