Reducing Cognitive Overload in Practicals by graphing with Excel

Confession, they say, is good for the soul. I regret to say that for far too many years as a Science teacher, I was in the habit of simply ‘throwing a practical’ at a class in the belief that it was the best way for students to learn.

However, I now believe that this is not the case. It is another example of the ‘curse of the expert’. As a group, Science teachers are (whether you believe this of yourself and your colleagues or not) a pretty accomplished group of professionals. That is to say, we don’t struggle to use measuring instruments such as measuring cylinders, metre rules (not ‘metre sticks’, please, for the love of all that’s holy), ammeters or voltmeters. Through repeated practice, we have pretty much mastered tasks such as tabulating data, calculating the mean, scaling axes and plotting graphs to the point of automaticity.

But our students have not. The cognitive load of each of the myriad tasks associated with the successful completion of full practical should not be underestimated. For some students, it must seem like we’re asking them to climb Mount Everest while wearing plimsols and completing a cryptic crossword with one arm tied behind their back.

One strategy for managing this cognitive load is Adam Boxer’s excellent Slow Practical method. Another strategy, which can be used in tandem with the Slow Practical method or on its own, is to ‘atomise’ the practical and focus on specific tasks, as Fabio Di Salvo suggests here.

Simplifying Graphs (KS3 and KS4)

If we want to focus on our students’ graph scaling and plotting skills, it is often better to supply the data they are required to plot. If the focus is interpreting the data, then Excel provides an excellent tool for either: a) providing ready scaled axes; or b) completing the plotting process.

Typical exam board guidance states that computer drawn graphs are acceptable provided they are approximately A4 sized and include a ‘fine grid’ similar to that of standard graph paper (say 2 mm by 2 mm) is used.

Excel has the functionality to produce ‘fine grids’ but this can be a little tricky to access, so I have prepared a generic version here: Simple Graphs workbook link.

Data is entered on the DATA1 tab. (BTW if you wish to access the locked non-green cells, go to Review > Unlock sheet)

The data is automatically plotted on the ‘CHART1 (with plots)’ tab.

Please note that I hardly ever use the automatic trendline drawing functionality of Excel as I think students always need practice at drawing a line of best fit from plotted points.

Alternatively, the teacher can hand out a ‘blank’ graph with scaled axes using the ‘CHART1 (without) plots’ tab.

Using the Simple Graph workbook with a class

I have used this successfully with classes in a number of ways:

• Plotting the data of a demo ‘live’ and printing out a copy of the completed graph for each student.
• Supplying laptops or tablet so that students can enter their own data ‘live’.
• Posting the workbook on a VLE so that students can process their own data later or for homework.

Adjusting the Simple Results Graph workbook for different ranges

But what if the data range you wish to enter is vastly different from the generic values I have randomly chosen?

It may look like a disaster, but it can be resolved fairly easily.

Firstly, right click (or ctrl+click on a Mac) on any number on the x-axis. Select ‘Format Axis’ and navigate to the sub-menu that has the ‘Maximum’ and ‘Minimum’ values displayed.

Since my max x data value is 60 I have chosen 70. (BTW clicking on the curved arrow may activate the auto-ranging function.)

I also choose a suitable value of ’10’ for the “Major unit’ which is were the tick marks appear. And I also choose a value of ‘1’ for the minor unit (Generally ‘Major unit’/10 is a good choice)

Next, we right click on any number on the y-axis and select ‘Format Axis’. Going through a similar process for the y-axis yields this:

… which, hopefully, means ‘JOB DONE’

Plotting More Advanced Graphs at KS4 and KS5

The ‘Results Graph (KS4 and KS5)’ workbook (click on link to access and download) will not only calculate the mean of a set of repeats, but will also calculate absolute uncertainties, percentage uncertainties and plot error bars.

Again, I encourage students to manually draw a line of best fit for the data, and (possibly) calculate a gradient and so on.

And finally…

If you find these Excel workbooks useful, please leave a comment on this blog or Tweet a link (please add @emc2andallthat to alert me).

Happy graphing, folks 🙂