Recent Posts
Featured Posts

Desert Island Tech: Excel and Mail Merge


Tropical Island View

I am picky about the technology that I choose to adopt in my everyday teaching practice. It has to be effective, efficient and win/win for students, teachers and managers. Mail merges are a tool that I would not want to do without.

Many, many sensible teachers use excel and some make good use of mail merge, but I am continually surprised by how many haven't discovered how much easier their lives would be and how much more effective their AFL could be with the judicious use of mail merges.

What is a mail merge?

For the uninitiated, mail merges were designed to automatically create variations on a template using an external data source (i.e., excel). The classic example would be using an excel spreadsheet that has the names and addresses of a bunch of people to generate personalised form letters and envelopes. It is very easy to do. I'm not going to provide a tutorial here because there are hundreds of much better ones out there, which you can google easily.

As a teacher, creating form letters might be useful for things like trips, but the real functionality lies in using mail merges in conjunction with data and marking to provide personalised feedback sheets for students. Here's an example of one I have been using to provide individualised feedback on a (very early) mock exam for my GCSE media studies cohort (OCR spec Unit B322). Admittedly, there is some pretty whizzy stuff going on behind the scenes in excel, but even a simple spreadsheet can be used to create helpful, student friendly reports, response prompts and AFL sheets:

Mockulator AFL.PNG

Convinced yet? Let me take you through which bits of this are part of the standard template (the same for all students), which bits are automatically figured out by excel (blue) and which bits are entered into the spreadsheet by me (yellow highlighter).

Mockulator AFLEdit.PNG

Once I had the excel spreadsheet up and running (about two hours of work, but this can be used over and over again), and my template created (half an hour), the only thing I had to do was put a point score for each of the five questions for each kid, and select the main area for improvement for each question from a drop-down menu: knowledge, examples, explanation, incomplete etc.

A number of excel formulas worked out the rest (if you are wondering, I wrote a general target relating to each of the 'areas for improvement' for each question - this is only about ten in total - and put a lookup formula in a hidden adjacent column to the field that would automatically select the appropriate target based on my drop-down selection). If you are interested in seeing the behind the scenes excel stuff, get in touch - I'm happy to share.

Once I had finished marking, I opened up the mail merge template which formatted my data into something useful for the student, clicked a button and printed off 25 fully customised, student friendly feedback sheets. The kids stuck them into their books, and we spent a lesson doing a bit of data analysis and action planning to make sure that the information that I was giving them was used effectively to improve their work and give them clear studying/practice targets.

Why it is good for the students:

It was very successful, and not only did the students get concrete feedback on where precisely they went wrong in every single question, it was a great way to embed some meaningful numeracy and statistical analysis in a media studies lesson. When I mark again, they will have perfect continuity so they can compare their new sheet to the old one. If a kid loses her book, I can just reprint her AFL sheet any time I like.

Why it is good for me as a teacher:

Once I had made the initial resources, it was vastly quicker than providing written summative comments on each question - and because it was so fast, none of the kids at the bottom of the stack had to suffer from half-assed marking based on teacher hand fatigue. Also, I now have a spreadsheet that has recorded marks and targets for each of my kids, which I can refer to when I mark future practice exams, write reports or identify students needing intervention. I can see at a glance from the spreadsheet which questions need more work and which kids would benefit from working together on practice questions based on their key skill competencies.

Why it is good for my department:

Teachers marking the same paper can use the same spreadsheet. Cell formatting options allow me to see which questions are causing a general problem across multiple class groups. I can identify a profile for each class group based on relative strengths and weaknesses by question or student group to allow effective resource sharing between teachers.

What can you do?