A self-marking spreadsheet

10/29/2013 12:00:00 AM
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:

=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:

  • 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:

Title Date Size
self-marking spreadsheet template.xlsm Mon Oct 21, 2013 11:02 AM 20.03 KB
Self-marking spreadsheet template.pdf Mon Oct 21, 2013 11:02 AM 260.70 KB

cross-posted on www.ictineducation.org

Terry Freedman is an independent educational ICT consultant with over 35 years of experience in education. He publishes the ICT in Education website and the newsletter “Computers in Classrooms."

comments powered by Disqus
Tweets
Photo GalleriesView All Galleries >
Acer C720-2844 Chromebook

(www.acer.com) The Acer C720-2844 Chromebook model delivers speedy performance, a quick boot time of seven seconds, and a matte anti-glare display tha...

Britannica ImageQuest

(www.britannica.com) Britannica Digital Learning has upgraded ImageQuest, a resource for schools and libraries that provides nearly three million rig...

ClassFlow

(www.classflow.com) Promethean has released ClassFlow, a free, all-in-one, cloud-based teaching tool that lets teachers create and deliver interactive...

Adobe Voice

(www.adobe.com) Adobe has released Adobe Voice, an animated video app for the iPad that lets users create and share video stories. The app incorporate...

DeskBoard

(www.boxlight.com) The BOXLIGHT DeskBoard mobile cart adjusts both height and tilt for the P8 ultra short throw interactive projector on a white surfa...

Core 36M

(www.bretford.com) Bretford has introduced Core 36M, a 36-unit charging cart that is optimized for Chromebooks but which also supports most tablets, l...

Edmentum Sensei

(www.edmentum.com) Edmentum Sensei is a mobile optimized solution that helps administrators visualize and track overall school, teacher, and student p...

HMH Player

(www.hmhco.com) HMH has released HMH Player, a new native app for iOS and Google Chrome that streamlines the learning experience for improved digital ...

Juice Power System

(www.bretford.com) Bretford has unveiled an easy-to-use modular power system with exchangeable power components. The Juice Power System uses unique &#...

LightSail

(www.lightsailed.com) LightSail Education has announced a literacy accelerator that combines books with in-text embedded assessments and real-time dat...

myON

(www.myon.com) myON has expanded its digital library to include a set of literacy tools to further personalize the reading experience for students whi...

Nervanix Clarity

(www.nervanix.com) Nervanix has released Clarity, an application that monitors student attention levels as they study and guides them to revisit conce...

MathBall

(www.robotslab.com) RobotsLAB has introduced MathBall, a smart sensor basketball and tablet system that offers curricula in algebra, pre-calculus, phy...

MobileAsset.EDU

(www.waspbarcode.com) Wasp Barcode’s MobileAsset.EDU solutions include everything administrators need to account for their assets, from software...

OpenEd Assessment Creation Tool

(www.opened.io) OpenEd has announced a free tool that lets teachers easily create assessments with the question types required by Common Core standard...

Panasonic TH-80LFB70U

(www.panasonic.com) Panasonic’s TH-80LFB70U interactive LED display features high-speed, multi-touch, interactive capabilities to promote partic...

penveu interactive display system

(www.penveu.com) The penveu interactive display system is a handheld device that turns projectors and large screen displays into interactive whiteboar...

PresentationPro

(www.califone.com) Califone has updated its PresentationPro speaker. The PA310 readily connects with computers, LCD projectors, mobile devices, intera...

PowerSync+ Mobile Companion App

(www.bretford.com) Bretford Manufacturing, Inc. has announced the availability of the companion app for its PowerSync+ enabled charge and sync produc...

PureCharge Carts and Stations for iPad

(www.bretford.com) Bretford Manufacturing, Inc. has debuted the PureCharge family of iPad and iPad mini charging carts and stations. By offering pre-i...

ProQuest Research Companion

(www.proquest.com) ProQuest’s new information literacy product, Research Companion, offers videos that guide users through the research process,...

Sphere2 & Class Send Student Engagement Software Platform

(www.averusa.com) AVer Information has developed a Student Engagement platform, providing teachers and students with the tools to transmit document ca...

TabChargeCT2

(www.averusa.com) AVer has released the TabChargeCT2 charge cart solution, which can hold up to 40 Chromebooks, iPads, Android or Windows tablets, lap...

VoiceLift with Instant Alert and Emergency Video Monitoring

(www.extron.com) The Instant Alert function of the Extron VoiceLift Microphone, combined with a PoleVault, WallVault, or PlenumVault classroom AV sys...

SMART Board 6065

(www.smarttech.com) The SMART Board 6065 is an ultra HD, 4K interactive flat panel that offers collaborative capabilities while ensuring lessons run s...

Gaggle Unity Partner Program

(www.gaggle.net) The new Gaggle Unity Partner Program provides free data integration services for all educational technology vendors. Through the Gagg...

Waterford Early Learning, Reading, Math & Science

(www.waterford.org) Waterford Early Learning Cloud can be used at home or to supplement lessons in classrooms. It can also be used for individual adap...

NetSupport School

(www.netsupportschool.com) The latest version of NetSupport School allows teachers to monitor and collaborate with any mix of technology. An enhanced ...

Camtasia

(www.techsmith.com/camtasia) TechSmith’s Camtasia gives teachers the tools to record lessons, create videos, and engage their audiences. Educato...

Panasonic 3E

(www.panasonic.com) Intel has teamed up with Panasonic to announce the Panasonic 3E, which uses the Intel Education 2-in-1 reference design. Designed ...