Holy Sheets! Minds-on Interactive Spreadsheet Adventures

Illustration with geometric terms and figures
(Image credit: Pixabay/geralt)

The golden rule in Google Sheets: To get an interactive version that you can edit, go to the File menu and select "Make a copy." This saves the file in your Drive account.

Are you ready to see the power of spreadsheets for mathematical, statistical, and scientific thinking? Let’s follow the path of using data to derive a mathematical model and then, using the model, to build an interactive simulation. A survey of 330 teachers found the No. 1 data analysis tool was  – wait for it – Google Sheets! MS Excel was third.  

Spreadsheets are the low-end of the computational food chain and can get your students started in relatively simple computations that can get really jacked-up if you want. 

Google Sheets offers the big advantage of being free and allows for online collaborative efforts in the classroom and out-of-class projects.

Data - Large and Small 

Collect your own (first-hand, you are performing the measurements)  

Collecting experimental data allows the introduction of simple measurement error analysis using centimeter rulers such as Investigating the Height of a Stack of Cookies (good first modeling activity with a “just-add-data” Excelet link). A good source of small real-world data sets (second-hand) is the Quantitative Environmental Learning Project (QELP) website. You can download data as an Excel file that can easily be converted to a GSheets file with no problem. 

Photo showing measurement error in diameter of plastic lid.

A common ruler error made by students. (Image credit: Scott Sinex)

Exploring Error in Measurements

There are two major groups of errors, random and systematic (adds bias), and both can be built into spreadsheet simulations such as “just-add-data” spreadsheets. 

So, how do you integrate error into a spreadsheet? Let’s examine an important measurement device, the ruler, using the Rulers and Measurement Error Excelet to explore the types of errors in a variety of ways. 

Big data sets with scatter

Many scientific groups make data available for downloading, and some even make it easy. The file format, .csv (comma separated values as a text file), can be read by Sheets and Excel. Data on web pages that are column-separated can be saved as text files and read by spreadsheets. If you are going to have students download data, check it out first to see the results! It can be messy.

Sea Level Change (and other global problems) is a large collection of GSheets spreadsheets in which data from NASA, NOAA, and other sites have been downloaded and are ready for plotting (see the guides below).  A large number of possible questions and tasks are included to aid instructors to modify to suit their classrooms.  Dealing with big data with scatter is an important task that students need to get involved in handling it!

How to get started from scratch

Dealing with Data in Excel 2013/2016 - detailed handout for Excel  (more about building later)

Dealing with Scientific Data in Google Sheets - Video instructions with an accompanying GSheets spreadsheets for hands-on practice

Mathematical Models and a Multivariable Approach

Deriving a mathematical model from data has become commonplace in math classes from middle school through college. These models, and so many science experiments, tend to be bivariate; however, in the real-world, things are multivariate plus measurements contain error.  So our approach needs to deal with variation, which comes in the form of measurement error and the possibility of the variation or change in other variables that we think are constant.  See the real-world function machine below.

Flow chart representing data inputs, outputs and error

(Image credit: Scott Sinex)


Do you need to teach linear regression? No, it’s just a line of best-fit. Goodness-of-fit, how good the linear regression fits the data, can be judged by the use of r-square and, as students turn to non-linear systems, the use of residues can be introduced. Novice students don’t see curvature in data especially if the r-square is near one. Converting from math, y = mx + b, to the actual variables, H = tn + e (for stacking cookies), is a task that needs lots of practice.

Just-add-data spreadsheets (pre-built with no writing spreadsheet formulae!!!) 

These experiments contain a variety of interactive simulation and data pooling too:

See What-if Spreadsheets for a wide variety of concepts in mathematics. These are pre-built spreadsheets available in both Excel and Sheets.

Simulations and What-if Questioning

Here is where more science process enters, as we ask the what-if questions that allow students to predict > test > analyze and then explain (yes, use words including math speak!). Students get to investigate how a variable influences the results by numerical experimentation.

Pre-built Interactive Spreadsheets (more than 265)

Build Your Own  Interactive Spreadsheets 

Online Collaboration

See our website, Data Pool in the Cloud, for examples and instructions to set up things in GSheets. The simplest thing to do is pool data for tasks. Gathering the data from groups or even individuals can be accomplished by simply sharing a spreadsheet with editing rights for students to enter the data or collect it via a Google Form. Now class statistics can be obtained and a class discussion can ensue. Discussions can be the whole class with the pooled data projected or set up groups and use the chat feature or comments placed on the spreadsheet in class, and yes it works! Want to play with one, try this - Collaborative Crowdsourcing Eye-ball Curve Fitting and, if someone else is there, open the chat.

Final Thoughts

Interactive spreadsheets bring an engaging pedagogy to the classroom that allows for numerical experimentation. These cover the complete pathway of data to mathematical models to simulations and allow error analysis to be introduced – and help students “get their think on!”  Getting students into building simulations is not difficult. 

Scott Sinex is a Professor Emeritus at Prince George's Community College in Maryland, where he taught chemistry for decades and served as the Department Chair of Physical Sciences & Engineering from 1995-2016. Learn more about his work and publications at https://sites.google.com/view/ssinex.