Academic Gradebooks using Google Sheets

Summative assessments (class tests) – whether it is house exams or state exams – are important. However, the storage and usage of this data can also be as equally as important!

Over the years, like all teachers, I have had many teacher diaries and record all summative assessments (class tests/monthly assessments) in these diaries. I tried to keep my own online records too, and enter results into the school systems – but over time it can be a bit too much! Record keeping is important – but analysing the data can be so much more powerful than having student results sitting in a teacher diary.

For the last two academic years, I have been exploring the use of Google Sheets in education. Normally, it is the first app to go to if you are making tables and/or graphs, etc. But Google Sheets can be used by every teacher, an example being for an academic gradebook.

The academic gradebook is simply using Google Sheets to not only store student assessment results, but also to provide test averages, student progression and a holistic view of the class via a distribution curve. In some sense, it brings all the data to life! I have manipulated the Google Sheets academic template to my own Irish context to provide an holistic view of the class performance as well as individual student progress.

Benefits of Academic Gradebooks:

(Please note: The following are all hypothetical results – this was used for when I provided training for teachers earlier this year. Results and grades are not representative of my students, it is for demonstration purposes).

  1. Firstly, it shows an overall grade performance based on averages on student assessments. Ideally, a normal distribution of grades would be preference, but in reality this is not always the case. In this example, I have not adopted the new H1/O1 Leaving Cert grade system as this class group is both HL and OL mixed, although it can be easily changed to suit grade levels in certain subjects.

2. It provides an average of all class monthly assessments. This graph provides very useful information on the progress of the class as a group. I have also used it to set “class targets” for assessments, such as 5% higher than last assessment, or other. This works particularly well as all students encourage and help one another to reach this common target. 

3. I applied conditional formatting to the grades. This means applying a colour system when looking at the grades as a collective. Deep shades of red are assigned to grades below 50%, and lighter shades of green to those above. This is something that proved very useful when looking at all results as a collective online, whereas a handwritten teacher diary would take longer to recognise the differences in numbers (no colour codes). 

4. The last, but most definitely the most beneficial aspect of the gradebook, is the generation of individual reports. These reports provide quantitative data, with conditional formatting applied and produce a graph of the student performance against the overall class performances. It also provides an overall average grade of the student, and the class. These individual reports have worked extremely well as an overview for students to visualise their individual results and progress. They have also worked quite well in recent parent teacher meetings, particularly 3rd and 6th year parents/guardians. 

These gradebooks are not aimed at replacing teacher diaries or any other school record system. It is simply designed to represent the data in a difference view or format so students (and teachers) can interpret the data. This year, many of my colleagues have adopted this method of analysing results and the feedback has been extremely positive. All it requires is data input, and let the formulae in the Google Sheets do the rest!

I am currently working on a new version that will include additional aspects, but there are many Google sheet templates available online for any teacher to try it out!

Please see the link below on other uses of Google Sheets for teachers! Thanks for reading! 🙂

Scroll to Top