A self-marking spreadsheet UPDATED

Updated!

A self-marking spreadsheet, by Terry Freedman

The following article was first published on 21st October 2013. I’ve made very few changes to it.

I like a challenge so I thought I’d try to create a self-marking spreadsheet in Excel. (Look, some men like fast cars, some like sport, and some like womanising. Me? I like spreadsheets. OK?)

I was inspired to have a go at this by someone called Lee Rymill, who uploaded a self-marking spreadsheet to the CAS resources area. However, I wanted to take it a few steps further…

Lee’s spreadsheet had the answers “hard-wired” into it, ie the answers were in the formulae, like this:

=IF(C3=25,"Correct","Incorrect")

I wanted to create a spreadsheet that was more generic.

Also, I wanted the spreadsheet to:

• Count the number of correct and incorrect answers

• Give the student feedback

• Tell the student where to to go for help or what to do next.

What I came up with seems to work, and can easily be customised for any test or quiz where a particular answer is either right or wrong. If you decide to use it, you will need to:

• copy the formulae down as far as you need to

• obviously save the file under a different name.

I really intended this as a proof of concept.

You could also use it as a means of demonstrating how Visual Basic for Applications (VBA) can be used in the context of Excel and other Microsoft applications (although there is some variation between applications). Even if you don’t intend to teach VBA as one of the required programming languages, this spreadsheet is a good demonstration of how programming can make life easier and more interesting for the user. It does this both in the background, and overtly:

If the student clicks the button labelled “See the rubric”, they see this message. If they click OK, the hidden answer worksheet becomes visible and they are taken to it. If they click Cancel, they see the following message:

A self-marking spreadsheet, by Terry Freedman

• Why not inject a bit of humour into your self-marking spreadsheet? See the screenshots above for examples.

• Hopefully, this will also demonstrate that spreadsheets don’t have to be as dry as dust: what’s wrong with a bit of humour in lessons?

• Other messages pop up, depending on whether the user clicks on “OK” or “Cancel”.

If you decide to give this a go, you’ll need to make sure your security settings in Excel will allow you to run a spreadsheet with macros. The PDF explains how it works. Feedback would be much appreciated. (I can think of one or two things I’d change myself, but I could go on tweaking forever!).

To access the files, subscribe to Digital Education, the free newsletter. Then go to the free subscriber resources area indicated in the welcoming email. Since writing this article I’ve added another spreadsheet, the Excel Grade Predictor. Enjoy!

Related articles