Use Conditional Formatting in Google Sheets
We are teachers. We look at a lot of student data. State test scores, quiz scores, grades, etc… Having the data automatically color code is super helpful. Conditional Formatting in a spreadsheet will do that for you.
Highlight the Correct Answer
If students are responding to questions, why not use a spreadsheet instead of a Google Doc? A spreadsheet is simply a table so many things we ask students to do in a Google Doc can be done in Sheets. Sheets offers some fun advantages over a document. One is the ability to use conditional formatting.
Click here for a spreadsheet template
Right Click on Answer Cell
Type the question in one cell and in the cell next to it have the student respond. Right click on the cell where the student will enter the answer and choose “Conditional formatting.” You can also find this option under the Format menu.
Set Conditions in the Side Panel
In the side panel, under “Format rules,” the default is “Format cells if cell is not empty.” This is a drop down list. Click on it to change to “Text is exactly.” In the box under this enter the answer. Use the paint can to choose the color the cell will highlight if the student enters the correct answer.
Press “Done.” It is that easy. Share a copy of the spreadsheet with the students and let them enter the answers to the questions. The spreadsheet will give the student immediate feedback.
Get Fancy
What are common wrong answers? Try “Add another rule” in the side panel for conditional formatting. If students may answer 6 instead of 5, click “Add another rule” and edit it so that if the student enters 6 it highlights red.
Highlight Data Scores
It is not unusual to have a spreadsheet with your student roster and columns of numbers for the students. On the 2nd tab of the sample spreadsheet above you can try out using conditional formatting to highlight anyone who scored over 600.
Highlight the cells that contain numbers and use the Format menu (or right click) and choose “Conditional formatting.” In the side panel set your Format rules to be “Greater than.”
Add Another Rule
The way conditional formatting rules works is it looks at the first rule you made and if the answer is YES it applies that formatting and does not look at any other rules. If the answer is NO then it goes to the next rule on the list to see if that condition applies.
In the side panel for conditional formatting click on “Add another rule.” This time you want anyone who is greater than 500. Use the paint can to choose a color for any cells that match that criteria.
Do it again for any values greater than 400, but choose a different color.
Change the Numbers
Conditional formatting reads the cell. Unlike manually using the paint can to highlight student scores, if you change the number in the cell the formatting will change. Try changing one of the lower scores into a higher score.
Let the Spreadsheet Do It
Another option is to click on the “Color scale” tab in the conditional formatting side panel. Choose the color for your minimum value and the color for your maximum value and the cells will automatically color code.