This is the final part in a series on basics for using spreadsheets for teachers. You can see the entire list of topics in the series at the end of this article.
Processing information with colors
It is well known that colors have an incredible impact on processing information. We instinctively know that green means go or positive and red means stop or negative. Using colors can help us quickly understand and interpret data. In spreadsheets, we can use something called Conditional Formatting to change the way our data looks, automatically.
If you think about the data you collect as a teacher, you might want student absences to show up in red or you might want to see test scores or grades in different colors depending on the percentage or mark. Let’s take a look at the basics of setting this up so you can do it on your own to meet your needs.
Highlighting blank cells
Sometimes, when we are putting in a lot of information into a spreadsheet, we want to quickly find out where data is missing. This often happens for me when I am writing anecdotal notes about student progress. We can use conditional formatting to highlight blank cells. Let’s take a look at some writing comments.
1. Highlight the cells that you want to apply the conditional formatting.
2. Go to the Format –> Conditional Formatting.
A sidebar will appear on the right hand side.
3. In the panel on the right, click + Add another rule
4. From the dropdown menu under Format Cells If… choose “is empty.”
5. The blank boxes on your spreadsheet will change color.
You can change the way the cells look by adjusting the cell fill and text color. You can also make the text bold, italics or strikethrough. Be sure to click Done after you have formatted the cells.
As you add text to a blank cell, you’ll see that your conditional formatting will go away. So this is great if you are looking through a bunch of information and you want to try to see what places you might’ve missed or what data you need to continue to enter.
Again, you can find the conditional formatting options in the Format menu. Click on Conditional Formatting to bring up the formatting sidebar on the right. Let’s look at changing the cells based on attendance.
1. Highlight the cells where you want to apply the conditional formatting
2. Go to Format -> Conditional Formatting
You can also right click and scroll down to select Conditional Formatting
3. In the panel on the right, from the dropdown menu under Format Cells If… choose “text is exactly.”
We are going to assume A is for absent and we want this to be highlighted red.
4. Type in the letter A in the box below “text is exactly.”
5. Format the color of the cell and the text in the cell as you like. I will make the background red and the text white.
You should see the cells with A in them update automatically.
6. Click Done.
If you want to add another condition, for example, you want to make the T for Tardy be yellow, you can highlight the same cells and add a new rule. Choose “text is exactly”, type the letter T in the box below and then format your colors. Finally, click Done. As you change information in the cells, the formatting will update accordingly. Test it out to see if it works!
Change colors based on numbers or percentages with a color scale
We can also use conditional formatting for test scores. Let’s take a look at math scores here by percentage. I want to get a quick visual of how students are doing. I can create something called a heat map.
In the Conditional Format sidebar instead of choosing single color, click where it says color scale. This will automatically give us a scale of colors. You can see that the darker colors are for lower numbers. And the lighter colors are for higher numbers. Just below the scale here, you can make some changes. Click on the default color scale. From here, you can choose from a couple of different options.
You can choose a preset color scale or make your own. Next to “minimum value” choose a color you want. You can also change the maximum value color and the midpoint value. When you are finished, click Done.
Change colors with a specific value
You can also set specific parameters for your conditional formatting. Using the same math score data, I’m going to choose which colors I want them to be based on the score.
1. Highlight your cells with the scores.
2. Go to Format -> Conditional Formatting.
3. On the single color selection in the “format cell if” choose “greater than”
4. I want all of my scores that are 90% or higher to be green. I will type in 90% in the box below.
5. Click on add another rule.
I want all of my scores that are higher than 60% but lower than 89% to be yellow.
6. Keep your choice to “greater than”
7. Type in 60% in the box below and format to fill the cells in yellow.
8. Add another rule.
I want the rest of my scores below 60% to be red.
9. Keep your choice to “greater than”
10. Type in 0% in the box below and format to fill the cells in red.
You can change the settings to be between numbers, less than, exactly, and so on. As the percentages change in the table, you’ll see them highlighted accordingly.
Cross out text based on entries in another cell (Advanced Level)
Now, let’s take a look at a situation that’s a little bit more advanced. We have to do a custom formula, which might be a bit intimidating, but I find it quite useful, especially when I’m making groups. Sometimes when you’re putting into groups, there’s often one or two students that you might leave out. This custom formula in conditional formatting can help.
I have a sheet tab labeled Reading Groups. I have my student names in column A. In columns C through E, I’ve used data validation to highlight these cells with my roster. So whenever I click on one of the arrows it will be a list of students. As I select a student for a group, I can use conditional formatting to cross out that student on my roster list. This will show me which students have yet to be placed in a group.
Setting it up
We will put the custom formula for conditional formatting on our student name list.
1. Highlight the student names in column A
2. Go to Format -> Conditional Formatting (or right click and scroll down to conditional formatting)
3. Add a rule
4. Under “format cells if” choose “custom formula”
5. Type in the following for the custom formula: =countif($A$2:F,$A2)>1
- The dollar signs means “absolute” or the row/column won’t change if I copy this formula to a different cell.
- The second $A2 shows the row where I want the formula to start
- The greater than 1 means that it will count, or make my conditional formatting true if a student name appears more than once.
6. Format the text. I will use a strikethrough on the words and change the cell fill color to gray.
As I put a student into a group, my roster will cross out that student for me. So this way, as I’m making my groups, it’s easy for me to see which students I’ve pl aced and who I still need to put into a group. Now, keep in mind if you put the same student in twice, it won’t change that it, it just crosses out the name. See if you can find other ways to use this type of formatting!
Wrapping it up
There are so many things that can be done with spreadsheets. I am constantly learning new things everyday. I often hear from colleagues that they wish they knew a bit more about spreadsheets. That is what inspired me to write this series. I hope that the topics gave you a bit more background and practical uses for spreadsheets in your classroom. In case you missed some of the first articles, here are the topics introduced:
• Highlighting Cells (Conditional Formatting)
I hope some of this information was useful to you. If you know of an educator that would benefit from this information, please do share these articles! Thank you.