The Client’s problem
Hyde Community College (HCC) is a secondary school in Tameside with almost 1,000 pupils.
In common with many commercial businesses, HCC makes extensive use of data; analysing, amongst other things, pupils’ performance data in assessments & examinations. The raw data is derived from a download from an external system which is then copied into an input sheet from which a series of pivot tables are used to create reports allowing staff to drill down to individual subjects & pupils.
The processes undertaken involve setting up a new input sheet for the next data set & re-establishing the pivot tables.
Again, in common with many commercial businesses, this takes quite a lot of time.
How we solved it
We suggested that the input data be converted into an Excel Table which would then allow new data to be added to the foot of the previous data set. We also suggested an additional column be added to show which data set (or “track”) the data relates to. The next stage was to link the pivot tables to the Table, rather than the original data range, as that would ensure that the Pivot Tables always related to the whole data set, even when new data was added.
By adding a field for the data track, HCC could still analyse by the relevant “track” as before, but having a whole year (or several years’) worth of data in a single table means that individual pupil’s progress can be evaluated throughout their whole time in school
The benefits for the client
These were summarised by Mr Rorick Houghton, deputy head with responsibility for curriculum & data: The initial and most obvious benefit was one of time saving. I’d say that a conservative estimate of the time saved with the new process is 15 hours of work per data drop. This is completed six times per year, so a saving of 90 hours per year has been achieved.
In addition, the re-establishment of pivot table links and refreshing of formatting was prone to errors that at first glance would not always be obvious to us. This had led to flawed analysis in the past. The new process is much more robust, in fact error free, which in turn has increased staff engagement with our data analysis.
Finally, whilst this approach worked very well for this particular student data analysis, the enhanced understanding, by key staff, of the value of table formats has led to further improvements in other analysis carried out using excel across the wider school.