Spreadsheets in Physical Science

When I get together with my friend Megan, we periodically wax poetic about our mutual love of spreadsheets.  Megan has a day job in data entry which requires her to know her way around Excel and uses that skill to easily track sales, calculate costs, and manage other records for her side business making and selling jewelry.  I got my introduction to spreadsheets as an undergraduate physics major when most of my lab courses required us to use Excel for our data analysis.  Since becoming a teacher, I've found Excel to be an invaluable tool when I want statistics on one of my tests beyond the average our gradebook software provides or when an administrator hands my PLC a list of students receiving poor grades in our courses, along with some demographic data on those students, and asks us to analyze our achievement gaps.  Once you know a few basics, spreadsheets, whether from Microsoft Excel, Google Drive, or one of the many other options out there, become an incredibly powerful tool for performing repetitive calculations and analyzing data.

A significant part of any good science class is performing labs, then analyzing the data, often by making a graph and doing a line of best fit.  In most cases, the time and effort of doing these steps by hand adds little to student learning, so using a spreadsheet can free up time and student attention for more the more important, and more interesting task, of extracting meaning from the trends in the data.  Similarly, there are many situations in physics where it is useful for students to see the results of some repetitive calculation in order to understand the connections between certain potential inputs and the resulting outputs, but there is often minimal value in having the students complete these calculations themselves every time.  Spreadsheets provide an opportunity to rapidly complete these calculations.  Having students design their own spreadsheet to perform the calculations could even be used to assess students.

With the rise of cloud-based suites, such as Google Drive and Office 365, spreadsheets can now incorporate collaboration.  Rather than limiting students to the data they can collect in a 55 minute class period (or at least what's left of it after attendance, getting the directions, cleaning up their station, and all the other little things that happen in a class period), students can use a shared spreadsheet to gather data as an entire class, producing a data set several times bigger than a lab group could working on their own.  The functions built into the spreadsheet software then give students the tools they need to analyze a larger data set without making the assignment overly tedious.  In the end, additional data typically provides a more accurate final result while creating a much richer ground for discussions of error.

Spreadsheets are a natural fit for the physical sciences.  Rather than spending their time on the tedious steps of linearizing data, graphing, and calculating slopes by hand, students can jump ahead to the excitement of seeing the way the equations were at play in the lab.  The focus of a science class should be on those beautiful moments when the underlying patters appear, not on the antiquated skill of how to line up your ruler just right when finding the line of best fit by hand, and spreadsheets make it possible for students to turn their attention to where it belongs.

Spreadsheet Project

References

Clintberg, B. (2010, September 30). Physics lab line straightening and graphing in a spreadsheet. Retrieved from http://www.youtube.com/watch?v=JOM4rS8-rxw

Dychko, S. & Rajan, A.  (2010, May 6).  Using spreadsheets.  C21 Physics teaching for the 21st century.  Retrieved from  http://c21.phas.ubc.ca/article/using-spreadsheets

Integrating google tools 4 teachers. (n.d.). Retrieved from https://sites.google.com/site/colettecassinelli/spreadsheet

Jazaeri, A. (n.d.). Spreadsheet physics. Physics & Astronomy Department, George Mason University. Retrieved from http://physics.gmu.edu/~amin/phys251/Topics/ScientificComputing/spreadSheets.html

Roblyer, M.D., & Doering, A.H.  (2013).  Integrating educational technology into teaching.  Boston: Pearson/Allyn and Bacon Publishers.