Conditional formatting: Tracking student progress with Google Sheets

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.




Using labels as coloured flags in Gmail

Keeping track of emails can be tricky – when an email is out of sight it is also often out of mind. Creating custom coloured labels to flag your messages is a great method for keeping track of emails you need to action or have delegated. In this blog post I’ll show you how to implement a custom label solution to suit your needs.


Create multiple signatures in Gmail

A common question I get asked when training our corporate and government clients is ‘Can I have multiple signatures in Gmail?’. While Gmail doesn’t have a dedicated multiple signature function, the good news is that you can use the Canned Responses lab instead. In this blog post, I’ll show you how to use Canned responses to create and insert multiple signatures.

How to organise your folders in Microsoft Outlook for Mac

How to organise your folders in Microsoft Outlook for Mac

If you’ve been using the same email account in Outlook for Mac for a while, you’ve probably ended up with a long list of folders that can be cumbersome and time consuming to navigate. In this blog post, I’ll share three tips you can use to better organise your folders for greater efficiency.


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...


Sign Up Now
To Get The Latest Updates


Holiday Rescue Maker Camp

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