Excel – Creating a Cumulative Frequency column in Excel

microsoft excelmicrosoft-excel-2016worksheet-function

I have a frequency table and I am looking to add a cumulative frequency column to it. The first 5 rows of the table looks like this:

Type Freq
1 1201
2 3910
3 2191
4 3654
5 9822

I just want to add a cumulative frequency column to the right. I did my research and attempting the following:

Assume the first Type value is in cell A2. I created a column called Cumulative Frequency, and then in cell C2 I wrote '=A2'. In cell C3 I wrote '=A2 + A3' and in cell C4 I wrote '=A2 + A3 + A4'. I then selected the cells C2:C4 and dragged the bottom right corner to the bottom.

However, instead of Excel understanding that I was creating a Cumulative Frequency column, it simply copied similarly what I had already wrote.

C5 = A5
C6 = A5 + A6
C7 = A5 + A6 + A7
C8 = A8

Any idea how I can fix this? It's a table of around 300 rows so I don't want to type the formulas individually.

Best Answer

Use SUM() with a variable range:


Note what is relative. As this is dragged/copied down the row number in the $A2 will change always summing the range above.