How to Calculate Cumulative Percentage in Excel (6 Easy Methods)

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.

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

“A running total of the percentage values occurring across a set of responses. The total will either remain the same or increase, reaching the highest value of 100% after totaling all of the previous percentages.”

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


Download the Practice Workbook

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


6 Useful Methods to Calculate Cumulative Percentage in Excel Datasheet

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 has 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.

Find cumulative percentage by manual approach

Step 1:

⇒ Select Cell C2

⇒ Tap on the Cell B2

⇒ Press Enter

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

Find cumulative percentage by manual approach

Step 2:

⇒ Now go to Cell C3.

⇒ Add B3 with C2.

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

Find cumulative percentage by manual approach

Step 3:

⇒ Choose Cell C3

⇒ Use Fill Handle to drag or fill down the cell to C11.

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

Find cumulative percentage by manual approach

Step 4:

⇒ Now select the whole Column D 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 D will turn into percentages.

Find cumulative percentage by manual approach

Step 5:

⇒ In Cell D2, divide C2(1st value from cumulative frequency)  by C11(Total Sales).

⇒ You have to lock Cell C11 by pressing F4 after selecting Cell C11 in the Function Bar.

Unless you lock this Cell C11, cumulative percentages will show as errors later for the rest of the cells in Column D.

If you need to be enlightened more about locking or changing Cell References then you can go here to find in detail on this term.

Find cumulative percentage by manual approach

Step 6:

⇒ Use Fill Handle again to fill down the Cell D2 to D11.

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

Find cumulative percentage by manual approach


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.

Find cumulative percentage by using histogram

Step 1:

If you don’t have the Data Analysis command under the Data ribbon then you have to enable it first.

⇒ From the File tab, go to Options.

⇒ Select Add-ins

⇒ Click on Analysis ToolPack, you’ll find Excel Add-ins inside the Manage drop-down

⇒ Press OK

Find cumulative percentage by using histogram

Step 2:

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

⇒ Tap on the Histogram option & press OK.

Find cumulative percentage by using histogram

Step 3:

⇒ Choose the Cell Range B2:B11 as the Input Range.

⇒ Inside the Bin Range, input the Range or Intervals.

⇒  Select F2 Cell as Output Range.

⇒ Mark on Cumulative Percentage & Chart Output.

⇒ Press OK.

Find cumulative percentage by using histogram

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

Find cumulative percentage by using histogram

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 range over those 10 years mentioned. You’ll be able to know which range of your sales count most or least in that span of years.

Read More: Calculate Year over Year Percentage Change in Excel (Advanced Technique)


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.

Step 1:

⇒ Under the Home tab, select Analyze Data from the Analyze group of commands.

A side window will appear like the picture below.

⇒ Choose Insert Pivot Table

Find cumulative percentage by using 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.

Find cumulative percentage by using pivot table

Step 2:

⇒ Double click Cell B3.

A toolbox named Value Field Settings will appear.

⇒ Select Show Value As bar

Find cumulative percentage by using pivot table

Step 3:

⇒ 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.

Find cumulative percentage by using pivot table

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

Find cumulative percentage by using pivot table


4. Find out the Percentage of the Unit Values & Running Total in Excel

Let’s find this cumulative percentage by applying another method now.

Step 1:

⇒ Select Cell B12.

⇒ Add all Sales values by typing =SUM(B2:B11) in the Function Bar.

⇒ Press Enter & you’ll get the Total Sales as 1441 Units.

Find cumulative percentage by determining percentage of sales

Step 2:

⇒ Now select Columns C & D.

⇒ Under the Home tab, choose Percentage from the drop-down in the Number group of commands.

Find cumulative percentage by determining percentage of sales

Step 3:

⇒ Click on Cell C2.

⇒ Divide B2 by B12, it’ll show the result as sales percentage in the year 2011.

Make sure, you’ve locked B12 Cell by pressing F4 after typing B12 otherwise all other sales percentages will be shown as Value Error because the Sales values will be divided by empty cells consecutively under B12 Cell.

Find cumulative percentage by determining percentage of sales

Step 4:

⇒ Drag or fill down the Cell C2 to C11 with the Fill Handle option.

⇒ Go to Cell D2, type =C2; it’ll copy the value from Cell C2.

⇒ Now select Cell D2 & add C3 & D2 Cells.

⇒ Fill Down the Cell D3 to D11 & you’ll get all cumulative percentage values right away.

Find cumulative percentage by determining percentage of sales


5. Use Sum Function to Compute Cumulative Frequency and Percentage

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

Step 1:

 ⇒ Select Cell C2 & type =SUM($B2$:B2)

By locking B2 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 C in the next step.

Find cumulative percentage by using sum function

Step 2:

⇒ Now use the Fill Handler in Cell C2 to fill down C3:C11.

You’ve just got the cumulative frequencies of all sales year by year.

Find cumulative percentage by using sum function

Step 3:

⇒ Select Cell D2 & type =C2/$C$11 which means you’re dividing C2 by Total Sales from C11.

You have to lock the Cell C11 as you’re dividing all Sales values from Column C by only C11 every time.

Don’t forget to enable Percentage format for Column D by choosing from the drop-down in the Number group of commands.

Find cumulative percentage by using sum function


6. Embed an Immediate Formula to Find out 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:

⇒ Select Cell C2.

⇒ Type =SUM($B$2:B2)/SUM($B$2:$B$11)

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 C, so we have to make sure the cells are locked by using $ signs before both Column Names & Row Numbers.

⇒ Now use Fill Handle to drag down the Cell C2 to C11 & the entire cumulative frequency will be displayed.

Find cumulative percentage by applying or using direct formula


Conclusion

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!


Further Readings

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo