Using seating plans to capture student data

UPDATE September 2013
The day after I created and shared this sheet in my department (June 2013) a copy was seen by one of our SLT - I was asked to share it at the following morning's whole school briefing. Since then it's been used by a large number of teachers across the school. It has also been downloaded from the TES website almost 350 times, and I know it is in use in several other schools.

I've just been asked by our headteacher (new since September) to share it again across all staff at our school on Monday.

The version now available via the link below has been updated following feedback from use both in my department and beyond - it includes the ability to display more data on the plan and seat more students. The formatting should be a bit more robust to layout changes too.

Original post...
Very functional post this - simply a tool for making seating plans more effective and easier to make.

Background
For all sorts of reasons it's really useful to have student data visible on teacher copies of seating plans. It helps when allocating seats to start with so that you take things like SEN status into account, but we've also found it useful to include target and current working at levels/grades as well.

Having the student's status vs target visible can be very telling if you develop a pocket of underachievement in a certain area of the class, which may not be so obvious on a normal class list. It can also be useful if you are looking to sit a strong pupil with a weaker one as you can instantly see where they're distributed across the room. Form an observation point of view it can be effective in indicating how differentiation has been used, and can also give a quick view of progress.

The problem with this though is that it can take ages to get the data into the plan, and then to keep it up to date can also be a real pain, both when moving students around the room or between classes, and also to update latest assessment grades into it.

As it's time consuming and is only really visible to the class teacher on a day to day basis it will often get pushed towards the bottom of the pile when things like marking, planning, teaching and life outside of school start squeezing your waking hours. We needed a solution that helps everyone in the department to create high quality seating plans and to keep them up to date....

A data linked seating plan tool
Following a bit of discussion and Excel fiddling in the department we've come up with something that takes the pain out of it all!

Basically you cut and paste your class list and key bits of data into one tab of the spreadsheet like this (this is fictional data made up by me for blogging purposes only; any similarities to real people entirely accidental!):

You only have to fill in the green bits - the sheet works out the comparison to target based on the data entered and key stage of the group. (useful when updating the CWA grades - only got to update one column). For KS4 it counts grades above/below target (note grade S means A*), for KS3 its NC sublevels above or below - could easily be tweaked for alternative grading structures.

This data will then be placed into correct place in the seating plan tab depending on where you put each individual pupil's number. It also has functionality to check that you've seated everyone somewhere in the room and also highlights if you've put someone in twice

The seating plan itself can be displayed at different levels...
Full data - for the teacher to use, and useful for printing for observed lessons - summarises progress and key "groups" info in one A4 sheet.

You can move the student within the plan by typing their number into the shaded green box next to each seat, their data moves with them automatically

If you want to display or print the plan to show to students you can choose to show them with or without the comparison to target, like this (with targets visible):

Or this (targets hidden):

Clearly this is specifically set up to the layout of one class in our school, however by dragging the boxes and the formulae they contain around the sheet it can be reconfigured to represent any classroom configuration. Row hiding to display data or not may take a little thinking about for less conventional layouts but it's all possible with a bit of fiddling.

Updating the CWA grades just requires overtyping or pasting the latest figures into the pupil list, the rest will automatically update

The outcome
I created 2 seating plans for new groups completely from scratch inside 10 minutes today, including cutting and pasting the data in and tweaking positions of students to get the right balance on each table. In the past this has taken me at least half an hour per group. I also know that I can adjust and update the plan really quickly in the future

I'm asking my whole department to use this sheet (or a tweaked version that fits their room layout) - it's just so worthwhile to have this kind of data really clear to you when planning. As they're all basically the same format it also becomes really easy for us to have a central saved record of plans in case of cover/absences.

Do you want it?
If you want a copy of the excel sheet you can get it here.

My department and I think this is really useful - I'd be interested to know your thoughts...