Google Docs: The Spreadsheets Dilemma

By | August 11, 2011

For the most part, I’m very happy with Google Docs. The ease of sharing and collaboration make it a useful classroom tool. The most recent updates make it possible to completely replace desktop word processors with Google Documents. Google Drawings has some limitations (in font formatting), but offers most functions found in Paint. The lack of animations and simplicity of Google Presentations is refreshing (though an update is long overdue).

Spreadsheets are great as well. Though not as intuitive as the others (you have to have experience with excel or a similar program to get the most out of it), it has most of the functions of traditional spreadsheet programs. It works very well for organizing individual and class data. You can have conditional formatting of cells (which can be fun and useful). You can insert pictures and text, and make simple charts. The charts are clearly not designed for scientists. As a chemistry teacher, the only type of chart that is remotely useful is scatter plot, and Google Spreadsheets does it well. My hope for any spreadsheet tool is for students to be able to demonstrate a conceptual and mathematical understanding of the relationship between the variables being studied.  What’s missing from Google Spreadsheets is the ability to add a trendline to the scatter plot.

What started with toying around with the new updates to Spreadsheets turned into an attempt to solve the problem myself. Here’s my attempt to create SOMETHING that can work for me and my students:

The template has 5 sheets. Sheet 1 is for raw data. Sheets 2-5 contain:

  • copy of the entered data from sheet 1, and instructions on how to manipulate
  • scatter plot
  • slope formula
  • y-intercept formula
  • correlation formula (that turns green when it is >0.99)
  • Extrapolation

One learning target for my advanced class is to be able to graphically determine the order of reaction with respect to a particular reactant.  Entering the data into a calculator and manipulating it there can be time consuming and error prone, and at times distracts from my ability to assess student mastery of the learning target itself. So I plugged some kinetics data into the template, and here’s what I got.

Google Spreadsheet:

vs. Excel

So the numbers are spot on. It would be relatively quick and easy to analyze the data (assuming computer access). For the purposes of kinetics, I think this is good enough. But would this be useful for discovery/modeling labs?  Can students still get at the conceptual and mathematical understanding of the relationships between the variables?

It looks pretty. But is it enough? Too much? How should I modify the template to make it useful? Or should I just scrap it?


Matt Greenwolfe on August 13, 2011 at 9:28 pm.

I spent some time this summer working on this problem, and used visual basic to program a graphing app that runs in excel. The goal was to draw scatter plots and trendlines easily by automating the tedious stuff, but to make the students think about the important issues. No automatic curve fitting. You have to type in the equation using the names of variables and constants that you defined yourself, then adjust the values to fit the data. John Burk blogged about it here: where there’s a test version. The latest version is more complete. try it and see what you think.


Matt Greenwolfe on August 14, 2011 at 12:49 pm.

Here’s a link to John’s blog again which includes a link to download the latest version. Also to clarify, I’m sharing this as a response to your question about what features a good graphing program to support inquiry should have. I could write a huge description, but easier to see what I think by checking out the actual program and seeing how it works.

I’m not familiar with how to program for google docs, but I think teachers would find it very useful to have a similar app available there. See what you think. I would love to collaborate.



Ms. Bethea on August 14, 2011 at 5:32 pm.

Thanks for sharing! This is exactly the idea I’m looking for, and works very well for linear data. I will play around a bit more after dinner.



Your email will not be published or shared. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>