I have created a HTML test (more theory-based test) using Excel and formulas to automatically mark the students’ answers. It took some time but I wanted to make something that would mark automatically and act as a quick class test to help refresh students’ understanding of HTML and the correct element layouts, syntax etc..
- 3 main worksheets which contain various questions
- A final marksheet that calculates the student’s totals
- Answers all in drop-down lists, no typing required
- An Input sheet for the teacher to change weighting of questions automatically if needs be
- Conditional formatting shows right/wrong answers with colour-coded cells and icons
- All marks automatically calculated and displayed (hidden from student) in another worksheet
- Students can only view marks once the teacher has unlocked the and unhidden the Mark sheet
- Teacher: Please unhide the marksheet when the test is done
- There is the student version, which is presented to the student and the teacher version which has all the sheets unlocked and answers and calculations visible
Please note : Office 2010 is the suggested version for this spreadsheet as the dropdown lists are made using the Data validation tool. Students select the correct answer from a supplied list. The marks may not work correctly if students type their own answers in.
Have a go and see what you think!
The file can be found in the Tech Teachers Shared Folder.