Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Easy Peasy Color Code Your Spreadsheet

easy peasy conditional formatting
Easy Peasy Color Code Your Spreadsheet
Easy Peasy Conditional Formatting in Google Sheets

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

Subscribe to the alice keeler newsletter

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

Add another conditional formatting rule

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.

Go Slow Online workshops: alicekeeler.com/goslow

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.”

color code 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.

color code

Do it again for any values greater than 400, but choose a different color.

color code


color code

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.

color code

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.

color code

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 28th or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session for a way to create dynamic and interactive digital lessons. The Desmos platform is completely free and allows for any topic to be created or customized.

Exit this pop up by pressing escape or clicking anywhere off the pop up.