Optimising School Options Columns
This page describes a simple Excel tool for optimising a school’s subject options columns. The tool can be downloaded for free at http://www.rhydlewis.eu/resources/SubOpsCols.zip. A video tutorial on how to use this tool is available at https://youtu.be/M1SN4Ryz0Ns. This software is also the subject of the following publication in the Journal of Learning Analytics:
· Lewis, R., T. Anderson, and F. Carroll (2020) 'Can School Enrolment and Performance be Improved by Maximizing Students’ Sense of Choice in Elective Subjects?' Journal of Learning Analytics, vol. 7(1), pp. 7587.
The article can be accessed here (pdf).
In many countries, students entering their final few years of study will be asked to choose a small set of elective subjects that they want to specialise in. In places such as the United States (APs), Germany (Arbiturs), England and Wales (GCSEs and Alevels), and Australia (HSEs), these choices will be given in a set of “options columns” (sometimes also known as “blocking structures”). They may look like this:
Please select one subject from four of the five columns 

(1) 
(2) 
(3) 
(4) 
(5) 
Maths 
Further Maths 
Art 
Chemistry 
Biology 
Media Studies 
Geography 
History 
Computing 
French 
Physics 
P.E. 
Spanish 
Here we see that the options available to each student are dependent on the way that the subjects are split into columns. In the example above, students cannot study Further Maths and Physics because they appear in the same column. On the other hand, they are free to study both Maths and Art, despite this often being an unpopular combination
This Excel tool helps teachers arrange their schools’ subjects into columns so that the number of students whose preferred options are met is maximised.
The main reason is that it assists the timetabling process because subjects from the same columns can be taught at the same time. Using the example above, we could have a timetable like this:
Monday 09:0010:00 
Monday 10:0011:00 
Monday 11:1512:15 
Etc... 
Subjects from Column 1 (Maths and Media Studies) 
Subjects from Column 2 (Further Maths, Geography, Physics) 
Subjects from Column 3 (Art, History, P.E.) 
… 
In the above example, the thirteen subjects are split into five columns. The total number of ways of doing this (the total number of solutions that we might have used) is 10,306,752. Things get even more complicated for larger numbers. For example, with 30 subjects split into five columns, there will be more than seven quintillion possible solutions. The following graph shows the number of subjects and the resultant number of solutions using five columns.
This Excel tool enables you to identify the best possible solution for your school from among these many options. Teachers simply find out what their prospective students want to study, and the software uses this information to produce a solution that maximises the number of students whose preferences are met.
The way that subjects are split into options columns is important for schools and students for several reasons.
· Our experience in the education sector indicates that in localities where schools compete for numbers, students are more likely to choose institutions that allow them to study their favoured options. Indeed, schools will often publicize their options columns online and during open days to entice prospective students. More students bring increased revenues.
· Allowing students to select their favoured options is known to be beneficial for their studies. Being interested in a topic is a mental resource that enhances learning, which then leads to better performance and achievement. From a school’s perspective, this is beneficial because it can bring higher rates of successful outcomes, which in turn boost outcomerelated revenues. Allowing students to study their favoured subjects can also improve student behaviour, attendance, and engagement, as well as reducing occurrences of students wanting to change their subject options midsemester.
· Maximizing the number of students whose choices are satisfied by the options columns reduces the need for schools to be forced into creating additional classes (and incur the associated financial costs to accommodate students whose choices are not met).
One of the main benefits of this tool is that it requires minimal investment of time. No staff training is needed, and only a basic knowledge of spreadsheets is required. Ultimately, the only information needed to produce your customised solution is a list giving the preferred subject options of each student. This information should be placed into the first worksheet in the spreadsheet.
A 
B 
C 
D 
E 
F 
Student #1 
Maths 
Further Maths 
Physics 
Chemistry 

Student #2 
Physics 
English 
Maths 
Further Maths 

Student #3 
English 
Physics 
Maths 
Further Maths 

Student #4 
Chemistry 
French 
English 


Student #5 
French 
German 
English Lit. 
Latin 

The example above shows example input for five students, one per row. We see that the first student wants to study Maths, Further Maths, Physics and Chemistry (first to fourth options respectively). Similarly, Student #2 wants to study Physics, English, Maths and Further Maths. This tool uses specialised algorithms to process this information and produce a customised solution that maximises the number of students whose options are satisfied.
Every school is different, and it is usual for individual schools to have additional needs.
· Some subjects may always need to be in separate columns, such as those taught in the same room or by the same teacher.
· Some subjects may need to be in the same column (e.g., pairs of subjects for which a student can take a maximum of one).
· Subjects usually have a maximum class size and a limited number of qualified teaching staff.
· Some schools also choose to prioritise students’ first and second subject options over others.
· Some subjects are offered as double subjects and therefore span two columns.
These requirements can also be specified within this software.
Simply download the program for free, unzip it, open it in Excel, and follow the onscreen instructions. A video tutorial is also available at https://youtu.be/M1SN4Ryz0Ns.
R. Lewis, T. Anderson, and F. Carroll
Cardiff University / Bishop of Llandaff Church in Wales High School / Cardiff Metropolitan University
Last Updated, Tuesday, 08 February 2022