MS Excel dynamic graphs

Quadratic graph as drawn using the Excel spreadsheet with slider controls

Use the ‘forms’ toolbar in MS Excel to link a slider control with a cell. Then you can make ‘dynamic graphs’. Projected onto a screen, you can ask students to predict what the result of a change is going to be.

You can download a spreadsheet with the straight line graph, y = mx + c, and two forms of the quadratic graph, y = ax2 + bx + c and y = (x + p)2 + q. The second form of the quadratic helps to explain completing the square like problems, and helps to explain how transforming a graph by scaling in different directions can generate any quadratic graph from the basic y = x2.

I use the spreadsheet mainly on a projector in a brief whole class exposition with Access level 2 and GCSE classes. I’d set up (say) a straight line with a gradient of 2 and an intercept of 1, then ask

  • what will happen when I increase the gradient…
  • and when I increase the intercept….

Next, I have an OHP or a second copy of the straight line worksheet available with a very different graph and ask

  • How do I change the gradient and intercept to change the graph on the spreadsheet to look like the one on the OHP?
  • What direction and by how much

This all takes about 15 to 25 minutes with directed questioning, examples that include positive and negative intercepts and positive and negative gradients, and a recap. There is then a worksheet with 10 graphs on (copied from the spreadsheet into Word) and the students work in pairs to find the gradient and intercept from the graphs using a simple ‘x step = 1 triangle’ method for the gradient.

I find the projected spreadsheet helps me check the understanding, especially in the negative quadrants (directed numbers being a new concept for most level 2 students). The speed with which new graphs can be set helps people ‘see’ the effect of changes.

If you download the MS Excel spreadsheet here, you will notice that I have fixed the scale on the XY scatter graph that draws the graphs – the graph then moves around a window of definite size rather than having Excel recalculate the scales to fit the data.

Graph plotters like OmniGraph have more advanced and flexible facilities for exploring graphs, and allow you to change the graph formula on the fly, but tend to be harder to set up in my opinion.

Comments are closed.