How can you generate rapid individual feedback and individual targeted emails?

Quality feedback is a cornerstone of assessment. What constitutes good feedback has been extensively covered in the literature and here on this blog. In this post, I’ll share how – through a few Excel commands, some mail merging and a macro – you can generate individual feedback rapidly and en masse. Once you have got to grips with the Excel commands and mail merging technique you can apply it in a number of different ways:

Individual question by question feedback for coursework tasks
Exam feedback based on scores within sections
Providing specific module information
Communication with emails tailored to the level of attendance or engagement

These pages cover a walk-through with screen-casts and example documents.

Inspiration from Peter Klappa from the University of Kent
Tech support from Neil Mayne SHU
Editing from Mel Green SHU

Background: It is not uncommon in HE at present to be faced with >200 assessments that need to be marked and returned with meaningful feedback within a 3-4 week timeframe. This can be a challenge when teaching and research are also fighting for attention. Assessment rubrics can really help here, where predefined responses are used to generate individual feedback. Although many VLEs allow you to create these yourself, they can be limiting to use and require a constant internet connection in order to mark.

A working solution is to use spreadsheets and mail merging to generate individual messages and feedback. The approach has a number of benefits:

Time-saving: Although it can take awhile to set the spreadsheet up in the first instance, you can save time in the long run. I am no Excel expert and it took around two hours for my first attempt. However, when marking 200 scripts I halved the marking time compared to other years (saving myself well over two days’ work). In the second run, the updating of the sheet took 30 minutes. When used for exam feedback, the numerical scores that were going to be returned to the exam board were used to generate individual student comments.
Consistency: As a consequence of the way the spreadsheet works, the level of feedback is fully consistent between markers, with no errors in summing up the final score. It is often the case that there are several markers working on the same assignment at the same time. Each can be working on their own section via sheets in a master document or marking the same question giving consistent responses.
Individual feedback: Each student receives rich feedback that is relevant to them. The comments are personalised and address the students by their first name.
Admin: Keeping track of attendance and engagement can be a time-consuming task and many of the commands can be used to help speed up this process, as well as generating emails.

Limitations: the approach does not solve all issues as preset responses do not always work for extended essays or assessments where there is a range of content.

advice-assistance-button-guidance-royalty-free-thumbnail

On the following pages I provide a walk-through of how to use Excel and mail merging, as well as sharing a very beta PDF generator. The ideas presented came following a talk by Peter Klappa from the University of Kent where he presented these methods and how he has been using them. I have adapted his ideas with help from Neil Mayne at SHU to generate feedback for problem-solving coursework and exams, as well as module management where students receive bespoke messages about attendance, engagement and coursework support. Each student receives either an email containing their feedback or a PDF that is bulk uploaded to our Blackboard site.

What does the walk-through cover? The first thing you are going to need is an Excel spreadsheet populated with student details. You can almost certainly download this from your own VLE. We are going to use data validation to generate drop-down lists. I will demonstrate a few Excel commands to extract numbers from the responses and then stitch responses together with =CONCATENATE, finishing with the =VLOOKUP command to select a response based on a number. To generate the feedback, we can either use mail merge methods to email the feedback directly or macros to generate PDF files.