Search this site

Get in touch

Contact Terry Freedman

Thought for the day

Each day a randomly-selected "law", observation or suggestion will appear here.

Last 100 articles
Free subscriptions

Free guide to using interactive whiteboards

IWB Guide Cover26+ suggestions and tips. Free to subscribers of Digital Education (please see the link below)

 Subscribe to our free newsletter, Digital Education!

 It's free. Signing up entitles you to various freebies. We use a double opt-in system, and we won't spam you.

Click the image above for a free sample edition.

Sign-up page.

The DfE Assessment Innovations series collated. This booklet is free to subscribers of Digital Education.

Be notified by email if you prefer:

Preview | Powered by FeedBlitz

The Amazing Computer Education Project Book

Remember this?

Amazing Web 2.0 Projects

It’s been downloaded over 35,000 times. I’m hoping to create a similar Computer Education Projects book, which will also be free. Find out how you can help by reading this article:

The Amazing Computer Education Projects Book

Digital Education

News, views and reviews. In-depth articles. Guest contributors. Competitions. Discount codes.

(Not necessarily all in the same issue, but each issue is full of good stuff nonetheless!)

Sign up for our free newsletter now!

Oh No!!If you can't find what you're looking for...

Assuming you’ve tried variations of your search term and checked the spelling without any luck, you may find the article Finding stuff on the ICT in Education website helpful.

Alternatively, if it’s not an article you’re looking for, try looking through the menus at the top of the screen.

E-Books for Sale

Want to make your ICT lessons more interesting?

Then Go on, bore ‘em: How to make your ICT lessons excruciatingly dull is just right for you.

Clustr Map
Terry Freedman's Social Profile
Powered by Squarespace

« Conference catch-up | Main | Three cheers for anti-plagiarism software »

A self-marking spreadsheet

Free download! 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:


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:

  • As soon as you open the spreadsheet it checks whether a sheet called “Rubric” is visible. If so, it hides it. (The Rubric sheet contains the answers!)
  • There’s a button that the pupil can click when s/he wishes to check which questions they got right or wrong. When the button is clicked, a box pops up:

    Why not inject a bit of humour into your self-marking spreadsheet?

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

Here are the files:

Enhanced by Zemanta

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments (2)

Why not use with Google Forms? Much easier and more versatile.
October 22, 2013 | Unregistered CommenterAustin Booth
Thanks, Austin, that looks useful. My intention was to see what could be done, and to demo how VBA and some handy formulae can be used together.

PostPost a New Comment

Enter your information below to add a new comment.
Author Email (optional):
Author URL (optional):
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>