A cumulative percentage is a statistical tool commonly used in analyzing scientific research, finance, and sales data. But what exactly is it, and what is its purpose?
Table of Contents
Cumulative Percentage is defined as a way of expressing frequency distribution. It shows the percentage of the cumulative frequency within each interval.
Essentially, it can be viewed as the percentage of the accumulated total over time (or over certain intervals).
It can be used to understand how much progress has been made over time.
If you look at the image below, the first two columns show the total sales for each month. The third column is the Cumulative Frequency (or the running total). The fourth column shows the Cumulative Percentage (or the percentage of the accumulated totals).
Looking at this table can already give us an idea of how much progress has been made by this pseudo business over time.
- When it started in January, it only earned 5% of what it is making today (December).
- After 6 months (or in June), it earned more than half (52%) of the overall total.
As you may have noticed, the Cumulative Percentage gives us a better visual representation of the growth (as compared to the Cumulative Frequency).
Please know that Cumulative Percentage is not only limited to date intervals.
If you look at the table below, the first two columns show the number of students that earned a particular exam score. The last two columns show the Cumulative Frequency and Percentage.
Looking at this table, we can conclude that 72% of the students only got a score of 70 and below, which could mean that 72% of them didn’t pass (if 80 is the passing score).
As you can see, cumulative percentages can be used in varying datasets and can be interpreted in many ways.
Now, I’ll show you how you can calculate the cumulative percentage of your data in Excel.
1. Using Formulas to Calculate Cumulative Percentage
1. Set up your dataset. Add the interval on the 1st column and the value on the 2nd column.
Remember to sort the records in the correct order. It should be sorted from oldest to newest if it’s a date interval.
2. Now, we’ll add the formulas of the Cumulative Frequency in the 3rd column.
On the first cell, write this formula: =B2
Note that it should point to the first value in the dataset.
On the second cell, write this formula: =B3+C2
This formula sums up the current amount and the cumulative frequency of the previous row.
Next, copy this formula to the remaining rows in the column using the Fill Handle.
Click on the cell with the formula and hover your mouse on the right side of the cell until the Fill Handle appears (or until the mouse cursor turns into a black plus sign [+]).
Once you see it, drag your mouse down until you reach the last row in your data set.
You should now see your Cumulative Frequency column all filled out.
The last cell in this column is the overall accumulated total since the start of the dataset.
We will use this cell to calculate the Cumulative Percentage.
3. On the fourth column, write this formula: =C2/$C$13
Change $C$13 with the last cell in your Cumulative Frequency. Remember to add anchors or dollar sign ($) in the cell reference so that it always points to this cell.
This formula divides the Current Cumulative Frequency with the Last Cumulative Frequency to get the percentage value.
Once you’ve added the formula, copy it to the remaining cells in the column by dragging the Fill Handle down.
4. As the final step, convert these cells into percentage format.
Select all the numbers in this column. Go to the Home tab and click on the percentage button inside the Number section.
And that’s it! You now have your Cumulative Percentage calculated.
Note that the last cell in the column should be 100%, reflecting the percentage of the highest accumulated value.
2. Using Pivot Table to Calculate Cumulative Percentage
Using Excel’s Pivot Table is probably the quickest way to get the cumulative percentage.
1. Select your entire dataset.
Note that your dataset should be sorted in the correct order.
2. From the Insert tab, click on the Pivot Table button.
3. The “PivotTable from table or range” menu will appear.
Select where you intend to add the pivot table – whether on a new worksheet or a specific range in the current worksheet – then click OK to continue.
4. The PivotTable Field List will appear on the right.
Drag each field to its corresponding areas in the pivot table.
The interval should be in the Rows area, and the value should be in the Values area.
By default, the Pivot Table will get the sum of the value field.
We will change it so that it displays the Cumulative Percentage instead.
5. Right-click on any cell containing the value field in the Pivot Table and select Value Field Settings.
6. The Value Field Settings menu will appear.
Change the Custom Name with the appropriate name for the field. You can name it “Cumulative Percentage”.
Go to the Show Values As tab.
In the “Show values as” dropdown menu, select %Running Total In.
In the “Base field”, select the interval.
Once done, click OK.
And that’s it! You now have your cumulative percentage calculated!
What’s great with this method is that if you have new rows added to your dataset, you only need to update the pivot table’s data source and you’re good to go!
Conclusion
Cumulative Percentage has various uses in different industries. No matter what your purpose for it is, I hope this tutorial helps you easily calculate it in Excel. Whichever of the two methods you choose, please remember to always sort your dataset in the correct order before adding the formulas or the pivot table.