Conditional formatting: Tracking student progress with Google Sheets

Published
18 April 2016
By mattbproadmin

With Google Sheets you can track student results and easily visualise how they are progressing.

The most common ways for teachers to visually represent student progress is through graphing the data. However, with conditional formatting in Google Sheets, teachers have another option for graphically representing student progress.

Here’s how to conditionally format your Google Sheet

Step 1: Open up a Google Sheet

Step 2: Enter your students’ details and assessment tasks results

This could be done for you by using Google Forms and self grading assessments. I have used a form that has been auto-marked by the Flubaroo Add-on in Google Sheets – see image above.

Step 3: Set up conditional formatting in your Google Sheet (this isn’t hard)

You just need to select the test scores, then click the Format menu and Conditional formatting,

From here, you click on the ‘Format the cells if’ button and set up your condition. If you were working with percentages and you wanted any student who achieved less than 50% highlighted red, you would choose ‘is less than‘ and type in 50. Note that you may want to select is less than or equal to to capture the number as well.

You can add other rules by clicking the Add another rule link.

Here is an example where students who scored 5 or less were highlighted red, between 5 – 6 were highlighted yellow and those that scored 7 were highlighted green.