Saturday, 15 June 2013

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.

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...


  1. Excellent resource - love the tabs - think need to make clear that can by changing the ks4 to ks3 cell m2 - changes to the other worksheet tab.
    Thank you for sharing

  2. Realise it's thin on instructions- that'll be part of version 2! Thanks for the comment.

  3. I started my seating plans using this today. Very useful and really clever. Is it easy to tweak to include reading age and MENA columns? Would you be kind enough to show me how? I'd like to win my colleagues over...I just know they will ask about these columns too ! Really impressive♡

    1. Could be really simple and suggest you could switch out the FSM or SEN data and replace it with the columns you want. If you need to include reading age and MENA (must admit I'm not sure what MENA is) as well as FSM and SEN then it would take a bit of tweaking - leave it with me and I'll let you know when version 2 is ready with the extra columns.

      Thanks for the comments - glad you found it useful :-)

  4. I have emailed our maths team your original one. I will have to see what they say. I'll let you know and it's brilliant that you are making time to tweak it for me♡

    1. See the most recent update for inclusion of extra data...

  5. Hi,
    I have just found a copy of your tracker on TES and think it is fantastic! I wondered is there any way of changing the calculations of the grades? My school used the traditional GCSE grades (A,B,C) but now give the students an indication of where they would be working should the system change from lettering to numbers (A1,C3, D2). I was hoping the tracker could take this into account and calculate if they are above, on or below target. Would really appreciate your thoughts.

    1. Thanks for the comments. Fairly simple to sort out - it's all in the lookup tables on the ks4 tab - if you need further help then find me on twitter and send me a direct message with the details. :-)

  6. Thank you so much for this. It's brilliant. I have a small problem (probably me just being dim) but when I copy and paste my KS3 levels data in it shows in status as #NA but when I type it in by hand it is fine. What am I doing wrong?

    1. Um - Don't know to be honest - depends on how it is pasting in. Are there any spaces or other characters in the cells you are pasting? So long as it is just a number with a, b or c it should work (remember to set the key stage cell to 3 so it works on levels rather than grades) - dm me via twitter if you need more help - might need to look at the data.