Google has updated how conditional formatting works in Google Sheets. Using data to help direct next steps in teaching is important. Conditional formatting color codes responses to help you more easily find which students are being successful and which ones are struggling. Previously I had posted on formative assessment tips which included using conditional formatting. This blog post updates the process for creating conditional formatting.
You do not need to start with Google Forms to utilize conditional formatting, but it is likely that you will gather your data through Google Forms. Using Google Forms in the classroom allows you to quickly gather data from students either through surveys or using the Form as formative assessment. The key is to get information quickly so you can adjust your instruction in response.
In the spreadsheet from the Google Form (or any Google spreadsheet) highlight the range you wish to apply conditional formatting to. Usually this is a column. Clicking on the column indicator (the column letter) highlights the entire column, to apply the conditional formatting to anything that does or will show up in the column. Right click to choose conditional formatting from the menu or under the “Format” menu at the top.
Add New Rule
Underneath the range is the option to choose from a drop down list the criteria for applying the formatting to the range. The default is that the formatting will be applied if the “Cell is not empty.” Probably not the most useful option, click on the drop down list to choose other criteria.
The option I use the most is “Text is exactly.” This helps me to color code the right answer in a column when I use a Google Form as a formative assessment quiz. If you have a free response question you would be interested in using “Text contains” to help color code the cell if it contains keywords.
Note that the order of your rules matters. If you have a rule to color the cells red if it contains the word “red” and a different rule to color the cells blue if it contains the word “hood.” If you have a list of rules the cell will be checked against the first rule. If the rule does not apply to the cell the second rule will be checked. This will continue until one of the rules applies. Once a rule applies to a cell the later rules will not be checked. The new conditional formatting menu allows you to drag the rules to change the order they are checked.
After choosing your criteria for formatting there is the ability to choose what the formatting will be. You can fill in the cell, change the text color, bold or italicize the font in the cell, etc.. The new conditional formatting allows you to choose some default formatting choices or you can use the little toolbar to select your own formatting options.
It is possible to conditionally format a cell based on the values in different cells. For example in my rubric sample (https://alicekeeler.com/rubrictab) when you mark the rubric values in column A the corresponding column in the rubric is colored yellow.
To apply a custom formula highlight the range of cells you wish to apply the conditional formatting to choose “Custom formula is” from the drop down list of formatting criteria. Start the condition with an equals sign to establish a pattern rather than a set value. In the case of my rubric I wanted the level 3 column in my rubric to highlight yellow if the corresponding value in column A had a value of 3. I want each row to evaluate against column A. Using the formula =A9=3 applies the pattern to the range. Using the formula A9=3 evaluates each cell in the column always to cell A9.