Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Plot Cumulative Histogram in Excel (with Easy Steps)

A histogram is one kind of graph which we use to show frequency distribution within some prespecified ranges. These data ranges are called ‘bins’. The histogram chart shows how many data points belong to each of these bins. This article will illustrate how to plot a histogram using cumulative data in Excel with easy steps and proper illustrations.


Download Practice Workbook


Steps to Plot a Cumulative Histogram in Excel

1st Step: Collect Data

The very first step is- to collect your data and gather them in an Excel spreadsheet.

sample data create histogram

  • Here we have collected 55 students’ grade points data (CGPA).

Read More: How to Make a Histogram in Excel Using Data Analysis


2nd Step: Find Maximum and Minimum Values in the Data

  • To find the maximum value in the range D5:D59, use the MAX function.
=MAX(D5:D59)
  • To find the minimum value in this range, use the MIN function.
=MIN(D5:D59)
  • Here, we have found the maximum and minimum values (2.00 and 4.00)

Read More: How to Create a Histogram in Excel with Bins (4 Handy Methods)


3rd Step: Add a New Column for Bins

The 3rd step is creating suitable bins. This is up to you. You can create bins with any width.

  • Here, we have specified 0.25 as the width.
  • You can use the following common formula and drag it down until you reach the maximum value 4.00.
=IF(current_cell>4,””,previous_cell+bin_width)
  • Here, we have first input the minimum value in cell E5 and then used the following formula.
=IF(E6>4,"",E5+0.25)
  • The output will be as follows.

note

This is an iterative formula. Iterative formulas cause circular reference issues. To make this type of formula work, press ALT+F+T and go to the Formulas section, there, mark the “Enable iterative calculation” box and press OK.

enabling iterative formula

Read More: How to Create Histogram in Excel Using VBA (With Easy Steps)


Similar Readings


4th Step: Employ FREQUENCY Function of Excel to Get the Frequencies That Belong to Each Bin

The FREQUENCY function returns the number of occurrences of a value within a bin_range. It returns one more element than the specified bins_array, in our case, we will delete that.

  • The common formula is:
FREQUENCY(data_array, bins_aray)
  • Write the following formula in cell F5.
=FREQUENCY(D5:D59,E5:E13)

Here, D5:D13 is the data_array and E5:E13 is the bins_array which stands for the range of values.

applying FREQUENCY function

  • Now, copy the cells F5:F14 and paste the values only, in the same place F5:F14.

  • Now, delete the last 0.

Read More: [Fixed!] Excel Histogram Bin Range Not Working


5th Step: Calculate Cumulative Frequency

  • Now, add a new column named Cum. Frequency and apply the following formula in cell G5.
=SUM($F$5:F5)
  • The common formula is:
=SUM(absolute_reference_first_cell:first_cell)
  • Now, drag the fill handle icon to copy the formula downside.

calculating cumulative frequencies to create a histogram

Read More: Difference Between Excel Histogram and Bar Graph


Last Step: Plot Cumulative Histogram from Recommended Charts

  • Now, select the whole range that contains Bins, Frequency, and Cum. Frequency columns, E4:G13.

select data to create a cumulative histogram

  • Then go to the Insert tab.
  • From the Charts group, click on the tiny Recommended Charts icon.

  • From the Insert Chart window, select the 2nd chart type and press OK.

Here the procedure ends. Look at the following image, we have drawn the cumulative histogram successfully.

Cumulative histogram in Excel

Read More: How to Make a Histogram in Excel with Two Sets of Data (4 Ways)


Conclusion

I hope this brief discussion will help in plotting a cumulative histogram in Excel. If you have any queries, please let us know in the comment box. Our blog ExcelDemy covers more such hot topics of MS Excel. Visit us and enjoy Excelling!


Related Articles

Masum Mahdy

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo