Creating histograms with Google Sheets

My approach to creating histograms for the past sixteen years has been to have the students determine the minimum, the maximum, calculate the range, divide the range by the number of intended classes to find the width, and calculate the class upper limits. The class upper limits then permit the use of the FREQUENCY function in spreadsheets such as Excel, LibreOffice.org, OpenOffice.org, Google Sheets and Gnumeric. While in Excel and LibreOffice.org the existence of a gap width or spacing setting allows the columns to touch, Google Docs did not provide this option, hence the chart seen below.


The data is from an exercise in which the students weighed packs of MMs.

This past week, however, a student noted that Google Docs now has a Histogram chart type as a chart type. One pre-selects the raw data and chooses the Histogram chart type. The sheet used in the illustrations below can be seen on line.


Note that by default Google Sheets chooses the minimum, the maximum, and the number of classes. These can, however be altered. The key is the first four steps of the procedure outlined above, specifically the three numbers in bold. Determine the minimum, maximum, and class width.


In the Customization tab scroll down to Bucket size and enter the desired class width, in this example 0.04. Scroll down further to Min and Max (which display when the horizontal axis is selected) to enter the data set minimum and maximum, 1.64 and 1.84. Note that one enters the minimum, not the minimum plus a width (the first class upper limit).



The result is a histogram with the desired number of classes along with a correctly labeled x-axis including the lower limit and each of the class upper limits. Excel can only dream of being that easy (those who learn to make histograms in Excel with its tortured Chart Layout -> Axis Titles -> horizontal axis title -> title below axis  chart mouse journey will know what I mean).

One can choose to omit the legend, change the color, or add a label to the y-axis. The gap width is set automatically to zero.


At the introductory statistics level, my primary goal is for the students to gain access to the shape of the distribution. From this perspective, the number of classes chosen is not necessarily a critical consideration. Thus far Google Sheets appears to select relatively reasonable numbers of classes - or buckets in Google parlance - providing information on the shape of the distribution.

Popular posts from this blog

Box and whisker plots in Google Sheets

Traditional food dishes of Micronesia