Topic outline
- General
- Welcome
Creating your mark book in a spreadsheet (like MS Excel or Google Sheets) will, in no doubt, be hugely beneficial to you! As opposed to the original paper based mark book, an electronic mark book, once set up correctly, will save you an enormous amount of time and will limit the chance of human error.
We will be taking you through six steps on how to create a mark book. For the purposes of this module, we will be using MS Excel but should you have Google Sheets, similar logic will apply.
So without further ado, let's get started on this practical module! We recommend that you create your own mark book as you work through the videos and tutorials. This mark book should be based on actual test results and will be required to be uploaded at the end of this session to count towards your assignment marks.
By the end of this module, you should be able to do the following using a spreadsheet programme:
- Construct a class mark book
- Add a column of figures
- Derive the average of a column of figures
- Create a formula to determine the symbol related to a specific mark.
- Creating a mark book using MS Excel
The steps that you will be taken through in the process of creating a spreadsheet mark book will be:
- An explanation of cells, sheets, labels and values
- How to create a mark book
- How to format columns and cells
- Adding columns / rows
- How to average column and / or row values
- How to create and apply formulas.
Before we start with creating your mark book, have a quick look at the introductory video on 'Getting started with Excel'. A Google Sheets link is included below should that be the spreadsheet application of your choice.
Watch the video below to find out more about step 1. Remember, follow on your own spreadsheet as the facilitator takes you through the necessary steps.
We recommend that you set up your own mark book with a 'live class scenario' which has actual test results which you can upload. Work through these steps using this 'live data'!
Once you have created your spreadsheet (and don't forget to save it) let's give you a few tips on how to format columns and cells.
- So you've created a mark book, what's next?
Now that you have populated your spreadsheet with the names and surnames of one of your classes, as well as put in some test results, let's start using the functionality which MS Excel (or Google Sheets) can offer us! Using this spreadsheet is supposed to make your life easier and up to now, you have been putting in all the effort - let's make the spreadsheet put in the effort now!
It's useful to know what the average mark was in a particular test. Well, using a spreadsheet, you will be able to get this information almost immediately!
Sometimes in a spreadsheet, we don't just want to find out the average or add a column of numbers, we may have more sophisticated requirements! For example, some tests may be out of 50 and others out of 10: you may want to convert the test out of 10, to the equivalent mark out of 50. To do this, you will need to insert a formula into your spreadsheet. You only need to write the formula into one cell, then you can automatically apply this formula to an entire column or row. Let's find out more....
- And now?
So, in six easy steps you have created your own mark book - well done! However, we have not even scratched the surface of possibilities that a spreadsheet can offer teachers. However, as you become more familiar with how your spreadsheet works, you will find added functionality that you'll be able to incorporate, either into your teaching or admin, that will make your life easier and save you time!To set you along your way on this journey of discovery, take a look at these few 'advanced tips' tutorials which may give you some new ideas!
- Global Education Coalition (GEC)
The following enrichment resources have been made available by GEC members:
- Teaching learners using a spreadsheet
Aside from using a spreadsheet to enhance your administration processes, we can also investigate ways how students might harness this technology in their learning. In this section, we will showcase an example and then challenge you to think how a spreadsheet might be used in your classes.
Spreadsheets for Natural Sciences
Have a look at the lesson plan below on using spreadsheets to teach Planets Relative Gravity. See if you can replicate the spreadsheet. Think of other subjects where you could use spreadsheets. One subject that springs to mind is Accountancy!
- Assignment
Hopefully, during the course of this module, you were able to create your own mark book which show cases each of the six steps which were covered. Feel free to include additional formulas and functionality which may not have been covered in these steps!Once your mark book is completed, use the assignment tool below to submit your spreadsheet. The marking criteria are detailed below.
- Conclusion, attribution and licence
The spreadsheet is better than a calculator when it comes to working out term and year marks. Instead of working out grades for each individual you can generate the marks for a whole class! The trick, however, is to keep the mark book running throughout the year so that when it comes to reporting time most of the data has already been captured.
The following OER were adapted to create this unit of study:
The following resources were consulted in this study unit. Full copyright but free to use:
ICT Essentials for Teachers by UNESCO is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.
All content not licensed under a Creative Commons licence is all rights reserved, and you must request permission from the copyright owner to use this material.
