How to make the most of pupil data
Using Excel & VBA to change the culture of learning and improve results in mathematics by giving better feedback. My experience in a UK Secondary School.
NOTE: all personal information and names are fictional.
The problem
As a Maths Department we noticed that our marking and feedback seemed to be ineffective for most pupils. Our regular way of recording pupil attainment and giving feedback — whether it was marking books or tests — was fundamentally based on giving pupils overall marks and grades. Pupils rated and ranked themselves against one another from the scores we gave. Those at the top felt great for being better than the others; the majority felt like they were doing fine because at least they were not as bad as those who scored lower; and those at the bottom of the class felt like mathematics was just not for them. In short, our whole approach to marking and feedback did little more than reinforce pupils’ self-perception about their capability in mathematics. It was like a cognitive straitjacket. It certainly did not give them — or their parents — much indication of what they could do to improve, or to break out of this mould.
And the worst thing was that we as teachers were spending at least as much time marking pupil work as we were preparing lessons.
How could we determine if our observations were right, and what were the factors that lay behind it?
A hypothesis
As I poured over the academic research I came across a fantastic paper entitled Unskilled and Unaware of It. Here Dunning & Kruger write, “people tend to hold overly optimistic and miscalibrated views about themselves”, particularly those with limited knowledge in the first place. In fact “people who are unskilled in these domains suffer a dual burden: not only do they reach mistaken conclusions and make regrettable errors, but their incompetence robs them of the … ability to realize it.” (Journal of Personality and Social Psychology 1999, Vol. 77, №6).
By focussing on giving pupils their grades, we were not giving pupils a clear picture of their mathematics, only a picture of their standing compared to one another. The feedback was not making them think about how they were learning mathematics at all!
This insight was given its proper classroom context in Black and Wiliam’s article Inside the Black Box. Feedback can be incredibly effective for pupils, they argue, but only if it gives each pupil “specific guidance on strengths and weaknesses … without any overall marks”, for exactly the reasons we had observed. Furthermore, pupils “must be given the means and opportunities to work with evidence of their difficulties … [feedback] should give each pupil guidance on how to improve, and each pupil must be given help and an opportunity to work on the improvement.” (Phi Delta Kappan 2010, Vol. 92, №1).
This paper provided me with confirmation that our observations about marking and feedback were right: overall marks and grades took pupils’ eyes off what really mattered, an honest look at their strengths and weaknesses and what steps they could take to improve.
When I brought this to the department, we all agreed that this was the way forward. We could be so much more effective if we provided each pupil with a clear picture of their individual strengths and weaknesses in mathematics, if we gave pupils the opportunity to work on their weaknesses, if we joined this up over time so that pupils could see success from their efforts, and if we closed the loop and made sure parents knew this too.
The only question was how to achieve this?
What could I do?
I experimented with something called question-level analysis: simple spreadsheets and websites which gave each pupil a feedback sheet based on their scores on each question in an assessment. It was close to what we needed, and trialling it with my classes gave pupils a buzz about seeing a true picture of their abilities in the different skills that made up their mathematics as a whole. However using these was limited to isolated tests: it offered a one-off version of what we needed, it gave individual feedback to every pupil, but it would not offer much more than that.
However it was a colleague from a previous school who provided me with the way forward. He had spent some time developing an Excel spreadsheet which aggregated question-level-analyses over multiple tests, and to him I am deeply indebted, as this showed me what could be achieved.
As a department we needed to collect data for every question for every pupil, and it had to be from enough tests to get a full picture of each pupil’s strengths and weaknesses across the whole subject. What we needed was a skills-level analysis, from which we could provide useful feedback, from which pupils knew exactly what to do to improve and see the success of their efforts, and which we could communicate to the right people.
The solution
At this point in my working with data (2016–17) I had a good knowledge of Excel and VBA. This seemed the best place to start as teachers were used to working with spreadsheets.
NOTE: I have subsequently discovered Python and Django offer much more effective ways to collect, store and visualise this data which overcome the key limitations of Excel, which I will write about shortly.
I created a spreadsheet into which teachers would enter pupils scores for each question. I coded each question in upcoming tests according to the maths skills which the question tested, and created an index of these codes which linked to an online resource which provided revision videos and practice questions for each skill. Finally I added to this a landing page where teachers could run the VBA scripts which would create the feedback sheets. I designed the feedback sheets around red-amber-green colour coding for every skill tested: a clear way to visualise pupils’ strengths and weaknesses by skill.
Calculating the feedback for each skill for each pupil would have been too slow to do within the spreadsheet, whether I worked on the spreadsheet using VBA or with lookup formulae, so to speed up the calculations enough to be useable I imported the data from the worksheets as variant arrays, transformed the data in memory to create new variant arrays, and outputted these direct to the feedback sheets, creating a separate sheet for each pupil as a new workbook. (Chip Pearson gives a fantastic explanation here; Mark Johnson gives some excellent tips on speeding up VBA scripts generally here).
Two final problems remained: how to enable the twelve teachers in the department to work on the spreadsheet at the same time, and how to make sure data entry was correctly validated. I knew from previous experience that shared workbooks and VBA were a bad mix, whether using legacy options or otherwise. I knew too that multiple users of the same spreadsheet potentially led to problems just as bad, no matter how well I thought I had locked, protected and validated the sheet.
I decided that minimising or removing entirely the user’s contact with the spreadsheet was essential. I separated the worksheet holding pupil data from the landing page, and made sure that whenever pupil data was accessed it was loaded into memory as a variable, or written back o the worksheet direct from the variable, and once the operation was complete the workbook was closed. I created UserForms in VBA to manage data entry and limit the options users could choose. Sharing would not be necessary, and validation would be taken care of programmatically.
Finally, with this new system ready to go, I encouraged the head of department to change the way our scheme of work was ordered so that during the year every pupil would sit a test every half-term, six in all, and we would no longer continue with any other type of marking and feedback (apart from our everyday activity in the classroom). Every test would result in a feedback sheet which gave each pupil their individual strengths and weaknesses (and we’d send them home too so parents had the information just the same: see Ron de Bruin’s super-clear website which shows how to automate Outlook using VBA) and we built into our scheme of work time for re-teaching and working on the weaknesses revealed.
An example of the VBA code I used to send emails:
The impact
This new system and the feedback sheets were introduced across the department in 2017 and within a matter of months had quickly created a wholesale cultural change in pupils’ approach to learning mathematics.
Pupils who were previously coasting, content in the skills they did not have — not knowing what they did not know — were suddenly working harder, and working independently in their own time. Pupils were using the right vocabulary about the skills they did not possess, and they interrogat ed teachers about what they could do to improve. Data from our online platform showed that the median pupil was watching maths videos and attempting practice questions 3 to 4 times more than beforehand throughout the year. Parents were getting in contact with the school in a way they never had before about concerning results and about what their child could do about their “red” feedback (turn them into green, of course!).
Furthermore, teachers now have at their fingertips a clear picture of pupils’ strengths and weaknesses, and use this to effectively re-teach and revise skills poorly understood.
Five years on (2022), it has been a fantastic journey to have seen pupils take ownership of their mathematics learning. As the department has continued to use this system and recorded more and more data, we have been able to give each pupil a comprehensive picture of their strengths and weaknesses which grows test-on-test, so that long before the final exams pupils and parents have known what they need to do to achieve. We have seen our final exam results at grade 7 (A) and higher grow from 20% to 33%, and at grade 4 © and higher grow from 85% to 92%.
It has been satisfying too to see our way of feeding back to pupils spread into other departments across the school as its value to pupil learning has been acknowledged, and maths departments in other local schools have started to build their own systems to do the same thing.
Best of all, however, is when I now speak to pupils for a few minutes. Conversations about their mathematics learning has been utterly transformed. Pupils really value the tests and look forward to getting their feedback sheet each time — although not without some nervousness because they know it will be a true picture of their mathematics. Pupils no longer rate themselves against one another so much as against what skills they have turned from red to green compared to last time. And they know exactly what to do to improve.
Feel free to download a basic version of this system in one file — the “Dashboard” as we call it —from my GitHub page.
Please read part 2 to see how I am using Python & Django to create a scalable solution and overcome the limitations of Excel & VBA — coming soon.