Making your Graphs Come Alive

Oftentimes it is helpful to show how things change over time.  This can be done on a normal excel graph by plotting time on the x-axis, and the time dependent function on the y-axis.  However, if the object is moving in two dimensions, it can be difficult to show all three bits of information (x, y, t) at the same time.

With a little bit of planning and a bit of coding, you can produce a series of graphs that show (x, y) at particular times (t1, t2, t3, etc.), and then show the graphs in sequence.  Before I learned about the method below, I assumed that all of the graphs would need to be plotted individually, and then combined together in a movie or .gif making program.

Here’s how the animated graph looks when exported as a movie.

This method is based in information I found at www.s-anand.net.  The example there used bubble charts, but here I’ll discuss scatter plots.  I also found some information at www.epdc.org  This provided some more useful code that I have adapted below.

The end result is an animated graph you can record and use elsewhere.

There are two methods that can work, and it is reasonable to do both with the same set of data on the same worksheet.

The first method automates the stepping process.  This allows a smoother transition between steps.  This is useful if you can make a screencast of the worksheet.  You can then use the video however you like.  In this case it won’t be quite as interactive as the second method.

The second method involves adding a scroll bar to the spreadsheet, and then clicking up or down in order to step through the times.  This is useful in situations where you can open the spreadsheet and step through it manually.

In order to do this, you will need to set up the developer tools in Excel.  Once you have done this, come back and carry on below.

Setting up the Spreadsheet

Here’s how to set up your spreadsheet.  I’ll use a projectile as an example.

For maximum flexibility, set up your spreadsheet as follows.

I’ve formed the habit of highlighting cells I want to change manually in green.  In this example, I want to be able to set my initial x and y positions and speeds, the time interval, and the acceleration due to gravity.  The yellow cell (A7) is where I am going to set the step number.  This will be changed automatically by code later in the process.

Cell B7 is set up by multiplying the time interval by the step number.  Cell C7 will be the x-coordinate.  Cell D7 will be the y-coordinate.  By plotting the x and y coordinates together, we can see the position at any particular time.

I have shown the formulas in the screenshot by pressing “CTRL + `”.  (The ` symbol is in the upper left hand of your keyboard next to the 1 key.)  Be aware that when you click the same combination again, the graph and cells will be stretched or squashed, which is quite annoying. The equations shown are simply the kinematics equations with the variables pointing to the appropriate cells.

Because we only have one pair of coordinates, you will only see one point on the graph at a time.  You can try setting different numbers in the yellow cell A7.  You will need to eventually set the axis limits so that the graph doesn’t change scale automatically for each time value.   You can do this by trial and error, or you could use the range and maximum height equations to determine the proper size.  In most situations, you will have an idea of the maximum values on each axis, so you can set them manually.

Another caution should be noted.  Since we are plotting position against position, you should try to make sure that the x- and y- axes show the same length scales.  For example, if the 0 to 100 interval on the x-axis is an inch long, the 0 to 100 interval on the y axis should be one inch long.   This is usually best done after everything else has been adjusted.  I’ll remind you again later.

Now we are ready to set up the “step” functions.