Conditional formatting: Tracking student progress with Google Sheets

18 April 2016

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

Conditional formatting in Google Sheets - great for showing student progress

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.

Conditional formatting in Google sheets

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

Conditional formatting in Google sheets - adding rules

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.

Conditional formatting in Google sheets - different rules

Step 4: After adding all your rules, click Done

Here is an example of a student progress sheet that has been conditionally formatted.

Conditional formatting in Google Sheets - great for showing student progress

Note: f you copy and paste a cell that has been conditionally formatted, the formatting will be pasted into the new cell and is maintained.

Other Resources

You may also like to explore the grade book template in the Google Sheets template gallery.

In the grade book template for Google Sheets you get:

  • An Overview sheet with students progress graphed.
  • A marks page linked to a-f grades.
  • A student summary graph.

All of these sheets in the Google Sheet are customisable.  You can check out the teacher grade book demo here.




Recent Posts

Here’s What
Our Clients Say

PR Industrya


In 2018, Using Technology Better designed and delivered a two phase post-migration training program for this New Zealand based graphic design firm. The initial goal of the training program was to reduce frustration with G Suite, with the long term aim of facilitating a change in culture and collaboration that can lead to transformative practices

Download your skills checklist to see how you score...


Choose Industry:

Which Skills Checklist?


Nice Move Allstar!
You're about to get
awesome value in your
inbox that's going to make
life that little bit easier & sweeter!


Pop in your details below and we'll send you the PLD Checklist 📧

First time applying for MoE Funded PLD?

Get the Latest Blog Posts, New Tips And Tricks


NSW DoE Leadership Webinars


NSW DoE Leadership Webinars


Holiday Rescue Maker Camp

Student to agree to and tick:
Guardian to agree to and tick: