How to Calculate Cumulative Frequency in Excel?

Frequency tables are an easy way to show how many times an activity has occurred.

A frequency table usually has three parts. The name of the activity, the number of times the activity has occurred, or otherwise known as frequency and cumulative frequency.

Cumulative frequency is the sum of the number of occurrences of an activity and the activities above it.

You can always create a frequency table manually on paper, but Excel is there to make our lives easier and faster.

How to Calculate Cumulative Frequency in Excel?

Making a frequency table in excel and calculating the cumulative frequency is very easy.

Let’s see how we can Calculate Cumulative Frequency in Excel.

What is Cumulative Frequency?

The cumulative frequencies would be the sum of the frequencies up to a certain sales price or chips category.

For example- The first cumulative frequency would be 25%

The second one would be 25%+12%= 37%

The third one would be 37%+15%= 52%

And so on.

This can be done manually on paper and inputted in Excel. But there is a much faster and easier way to calculate cumulative Frequency in Excel.

Let’s see how to do that.

Frequency Table for Cumulative Frequency

Frequency-Table-for-Cumulative-Frequency

This is a normal frequency table showing the price of different chips and the percentage or frequency of sales.

Formula for Cumulative Frequency

First, we need to calculate the first cumulative frequency which is always equal to the first frequency. In this case 25% or cell F6.

So, in G6, input the formula “=F6.”

Formula-for-Cumulative-Frequency-1
Now, the second cumulative frequency is 25%+12%= 37%.

So, in G7 input the formula “=G6+F7.”

Formula-for-Cumulative-Frequency-2

After inputting the formulas, the table will look like this.

Formula-for-Cumulative-Frequency-2-1

Now, you can continue to input the formulas to calculate cumulative Frequency in Excel. However, there is a clever trick or a shortcut.

Select, the second cumulative frequency and hover your mouse over to the bottom right corner of that cell to bring out the black fill handle.

Now, press your left click twice, and voila.

Formula-for-Cumulative-Frequency-3

You have successfully calculated cumulative Frequency in Excel.

But there is always more of what you can do to stand out.

You can graph the cumulative frequency to make it more visually appealing and your stakeholders can also understand more clearly what your sales look like if you calculate and graph cumulative frequency.

So, let’s make a chart to visualize the cumulative frequency that you calculated in excel.

Creating the Cumulative Frequency Chart

First, select the price column, and then holding the CTRL key select the cumulative frequency column.

Creating-the-Cumulative-Frequency-Chart

Then go to insert ribbon and from the chart tools select “Recommended Charts”.

Creating-the-Cumulative-Frequency-Chart-2
A dialogue box will pop up from where you can choose your preferred chart option.

Creating-the-Cumulative-Frequency-Chart-3

I prefer a bar chart which is the second option in the recommended chats. So, I select the second option and click ok.

Creating-the-Cumulative-Frequency-Chart-4

Here you go, a more beautiful and clear understanding of sales of different price levels.

There you have it, follow these steps and in a very short time you can calculate cumulative frequency in excel and if you want to go further you can also create charts for the cumulative frequency all in excel.

2 thoughts on “How to Calculate Cumulative Frequency in Excel?”

Leave a Comment