Tuesday, May 28, 2013

Google Spreadsheets and Automatic Feedback for HW

Google Spreadsheets provides a powerful spreadsheet program that will blow Microsoft Excel out of the water. One of my favorite reasons is that each cell in a Google Spreadsheet is connected to Google Search. I will discuss that in a later post but for today I want to introduce an easy way to collect student responses to homework and provide immediate feedback. This is done with minimal effort on the teacher's part.

I am going to show a math example but this can be done with any fill-in-the-blank type answers. The idea behind this is to use conditional formatting to tell the spreadsheet to do a certain task when a certain value (answer) is placed into a cell. It is really cool for students to see their cell light up red when they have a wrong answer or green when they have it correct. If it is wrong that is the signal to the student to try again (relax teachers, this example is just for a homework/guided practice, for formal assessment use Google Forms).

Follow these steps below to setup a spreadsheet with conditional formatting

Glossary:
Conditional formatting- used in spreadsheets to alert the spreadsheet to perform a certain task upon entering a particular value into a cell. 


  1. Create a Google Spreadsheet that will serve as your answer document. 
  2. Use your knowledge of spreadsheets and cell coloring to make it look cool. See one I have created here
    1. Once you get an idea of how this works and where you want your students to place their answers go on to step 3. 
  3. Right click (CMD Click for Mac) on the cell where you want students to place their answers and choose Conditional Formatting. To reveal the figure below. 





  • Click the down arrows to reveal the options for the type of conditional formatting you would like to apply in the cell 


  • Any of the formatting types in the image above will work for this type of assessment. The thing that the teacher must decide is if he or she will require an exact answer or would a range of answers could be correct. For example, if you were doing a physics homework where the answer must fall in a range to be correct then choose "is between". 
  • Any rule you place in a particular cell is for that cell only. 
  • In my example for the sample HW answer document, I wanted only exact answers so I chose two rules: 
    • Rule One for text is exactly then turn background Green
    • Rule Two text does not contain then turn background red denoting a wrong answer. 
Apply as many rules as you want. Make it fun!



Share the spreadsheet with your class. This can be done many ways, but remember to set the shared sheet to view only. This will allow the student to see the sheet but they must make a copy before they can edit it. Once they make a copy it is in their Google Drive and they are the owner. If you inadvertently shared the spreadsheet without the view only option everyone would be able to edit each others spreadsheet. This example is for practice and intended for immediate feedback so students can work through certain tough problems at home. This can also be edited on a mobile device. 

For more on how to use Google Apps for Education in the classroom, check out my book Google Apps Meets Common Core   http://goo.gl/1DXNW