Conditional Formatting

Imagine you have created a series of awesome Google Forms, or you are getting a bunch of spreadsheets from a recently Galileo assessment you've given.  You get all this data on your students' progress in multiple sheets, and you just aren't' sure where where to get started.  You have all their grades, and you import them to your SIS.  Some students you may have a quick conversation with, but for the most part, the assessment is done and you move on.

In an ideal world, you'd spend some time drilling down the data, looking for trends, and adjusting your teaching to compensate.  A very worth action, but difficult to do regularly with the various other responsibilities you have.  What can you do?

Conditional Formatting can help by color coding cells according to value.  This can help highlight score ranges so you know what stuck and what didn't.  Here's a quick guide to getting started.

Conditional Formatting Step by Step

Step 1

Take a look at a sheet that has the results of a quiz you created.  Or, use this example sheet to get you started.

Step 2

Say you want to highlight the answers to see which questions most students got correct, and also if there were any trends with incorrect answers - perhaps there was a common misunderstanding somewhere.  Use the following color coding as a suggestion:

• Correct answer is green
• Incorrect answer 1 is yellow
• Incorrect answer 2 is orange
• Incorrect answer 3 is red

With the answers color coded like this, you should be able to glance at the results and get a solid idea of your students' understanding.  Here's how:

Step 3

1. In this sheet, go to the "Format" menu and select "Conditional Formatting".
2. In the sidebar that opens on the right, you'll see that you have a few options.
1. Under "Apply to range, select the sells where the data appears.  Click on the grid, and select all the cells you want to include.  Then click "OK".
2. Under "Format cells if...", select "Text contains".  In the box under that, type in the correct answer exactly as it appears in the sheet.
3. Under "Formatting style", choose the color fill icon and select "green"
3. You should now see all your correct answers (for question 1) highlighted in green.
4. Repeat the above process, but substitute the correct answer for each of the three incorrect ones, and select the appropriate colors.