Box and whisker plots in Google Sheets

And then everything changed. For the first time since the loss of Gnumeric a spreadsheet was displaying a box and whisker plot. To back up to the beginning, MS 150 Statistics was built around spreadsheets. Students in the course were not statistics majors and the overwhelming majority were not in a scientific field. Most would wind up working on office desktops for which they would not be an administrator. Downloading R would not be an option and R would be steep learning curve for some for whom computers are an unfamiliar technology.

Spreadsheet based statistics, for all the faults and potential for error, would be at the center of the course. The use of Ubuntu in the classroom meant using or Gnumeric. Gnumeric provided the capability of including box and whisker plots. A change in computer laboratory technology to Microsoft Windows five years later saw the use of Gnumeric on Windows - until Gnumeric stopped supporting Windows. A further change of my work top to OSX forced the retirement of Gnumeric as a spreadsheet supported by the course. Besides, the Schoology learning management system that I had adopted could not convert Gnumeric files. Excel, for all the complexity of the ribbon interface, saw increasing use by students.

For box plots I turned to BoxPlotR, although the package had some timing out issues for the class.

Although I had played with Google Sheets for eight years, the lack of the "t" functions (TINV, TDIST, TTEST) limited the usefulness of the package. The recent introduction of the "t" functions, the full functionality of the LINEST array function, the ability to integrate tables and charts into Google Docs, and the integration with Schoology converged to make Google Docs a viable choice. I was missing only box and whisker plots, but then,, and Excel also have no box plot option.

I had overlooked add-ons, though I was vaguely aware such existed. With increased use of Google Docs in association with Schoology assignments, I began exploring add-ons.

And discovered that Google Statisticians and Engineers had produced a Statistics add-on.

That does box plots. Oddly enough, as of late 2017 and early 2018 the add-on no longer appears in a search of add-ons as seen above, but the add-on still exists.

Including outliers.

And histogramsAnd density plots.

Including, again, outliers. In the number of green MMs found in a case of MMs.

Even Normal Quantile Plots.

And nothing was the same ever again. There was clearly no reason to ever use LibreOffice nor Excel. There were only a few edge cases in which Excel or LibreOffice would still be useful - fitting a second order polynomial on an xy scattergraph for example. Google Docs and Sheets now has the capability to support all of my courses - statistics, physical science, and ethnobotany. Yes, there are still some gaps, but they can be filled. Such as the inability to add a trendline and trendline equation to an xy scattergraph. But the work around is to use the SLOPE and INTERCEPT spread sheet functions. I already have that work around on the "material to be added" list for my physical science text.

Now I am left with much to ponder. Some experimenting with the automatic histogram generator suggests that Google Sheets makes reasonable choices. My present process of having the  statistics students build class upper limits by hand and then use the frequency function is complex and confusing. The goal was a histogram, should the machinery be tossed in favor of using Google Sheets add-on? Should I simply teach the students to use the tool and how to interpret the results? Everything changed.


  1. It looks like the addon is now gone.

    1. The add-on is no longer available, changes were made in my text and curriculum to reflect this loss.

    2. Hi Dana! What have you switched to? I'm starting an intro statistics class this fall and was leaning towards Google Sheets for similar reasons (more applicable after the class for the majority of my students who aren't going to be doing hard science) but am similarly disappointed that there is no native box-plot functionality.

  2. At present I am using The students are able to copy and paste their data directly from Google Sheets to BoxplotR. BoxPlotR includes a number of display options. The text now reflects this loss, the section on Google boxplots is deprecated and will be deleted if the tool does not reappear. See the section at:
    For including the boxplot in homework the students are usually using screenshots that they insert into their submitted Google Sheets.

    I would note that since spring 2018 I have had access to Schoology Institutional edition LMS with a Google Drive Assignments add-on that integrates the learning management system with Google Docs and Sheets. Other LMS may have similar integration options. My integrated use can be seen in the presentation at:

    1. Thank you! Very helpful


Post a Comment

Popular posts from this blog

Areca catechu leaf sheaf petiole plates

Setting up a boxplot chart in Google Sheets with multiple boxplots on a single chart