Create easily identifiable data that stands out with your Google Sheets.
Visualize student results with Google Sheets
- Add Activity to Favorites
Create easily identifiable data that stands out with your Google Sheets.
As education becomes increasingly guided by data, the need to effectively analyze and interpret that data is more crucial than ever. With Google Sheets, you can quickly collect and analyze key student data, such as grades, assignment completion, or attendance.
One way to make key data more visible is by applying conditional formatting. Conditional formatting is a spreadsheet feature that can add visual emphasis to your data. You can use conditional formatting rules to format cells to turn a specific color when they meet certain criteria. This allows you to easily identify data that stands out and may help you adjust your teaching. For example, you might set a conditional formatting rule to turn cells with low assignment scores red to identify students who need additional support with content or skills.
Follow the steps below to learn how to apply conditional formatting to make your spreadsheets more visual.
Now it's your chance to use conditional formatting.
Open a spreadsheet that contains data you would like to visualize. Select the cells you would like to apply conditional formatting to.
Then, open the Format menu and select Conditional formatting.
In the side panel that appears, choose a condition that must be met from the Format cells if… drop-down menu.
Depending on the contents of your cells, you can choose from a variety of conditions, such as if the cell contains specific text, if a number is greater than, less than, or equal to a certain value, or if a date is before or after a deadline.
In the formatting style section, choose how cells will appear if they fit the condition that you set in the previous step. You can choose to bold, italicize, or underline the font, strike through the text, change the text color, or change the cell’s fill color.
Any of these options can be used to draw attention to important data and help you gain insights into ways to improve your lessons to better meet your students’ needs.
Once your conditional formatting rule is complete, click Done to implement it or Add another rule to create a new rule for the same set of cells. You can apply multiple conditional formatting rules to cells to cover a variety of conditions.
For example, if you want to visualize which students are achieving scores above and below a specific value, you might set one rule to change the cells red if they are less than the value and a second rule to change the cells green if they are greater than the value.
If you would like more practice with conditional formatting, check out this video lesson.
There are many ways that you can apply and customize conditional formatting. You can even combine conditional formatting with formulas. Read these teacher quotes and explore the examples they describe in this spreadsheet to see other ways you might use conditional formatting to visualize data.
Add a color scale
“I organize a list of all my students and the books that they are reading into a reading log. Once a week during our individual reading time, I check in with each student and update their progress in one column using percentages. There are different shades of green so the higher the percentage they have completed of their book the darker the green color.”
Identify specific text
"To ensure students are engaging in my class, I use a spreadsheet to track student participation. At the end of each week, I assign each student a rating of frequently, occasionally, or rarely. To help me visualize patterns in student participation, I use conditional formatting to make cells containing each rating turn different colors. To create a conditional formatting rule for specific text, I select Text is exactly in the Format cells if drop-down and then I type in one of the ratings. Then I add another rule for the other ratings and change the color."
Format an entire row
“When I am tracking which students have and haven’t turned in their permission forms, conditional formatting allows me to format entire rows. To highlight a whole row if the value in column B is “Late,” I select all the rows I want the rule to apply to, open the Format menu, and select Conditional formatting. In the Format cells if drop-down, I select Custom formula is and use the formula:
=$B2=”Late”
This highlights not only the status of the permission form but the student's name, making it extra clear who I need to follow up with.”
Create a heat map
"I use the Color Scale option in conditional formatting to create a kind of heat map in my gradebook. Different shades of red are used to differentiate lower and higher scores. This helps me to quickly identify students who may need additional support or assignments that many students did poorly on."
Consider the following and make a note of your answers.
Once you have thought of data you would like to visualize better, make a copy of the conditional formatting examples spreadsheet, create a new sheet within the spreadsheet, and add your data. You can also open a new spreadsheet in Google Sheets for your data. Then, experiment with different ways to use conditional formatting to improve your ability to identify trends or key data.
Imagine you want to create a rule to turn cells with low assignment scores red to identify students who need additional support with content or skills. Which feature of Google Sheets could you use to do this?