Animate your Excel charts

When creating a chart it’s not unusual to find there are so many labels on the X-axis (the horizontal one) that they become illegible. A creative solution is to make a smaller chart and slide the points left or right, as though moving a window along a line. Here, we provide a simple example in Excel that you can adapt for your own use.

All the cells of the data table are formatted as numbers because the Scroll Bar control doesn't understand dates. To follow this example, enter 1994 in cells B2 and B8. Right-click on B8 and drag it to T8.

On the Shortcut menu choose Series, Linear, Step value 1. This gives you the years 1994 to 2012. In the range B9:T9 enter numbers randomly. This example uses numbers in the range 430 to 850. Enter =B2+1 in cell C2 and drag that to T2 which will have the formula =S2+1.

In cell B3 enter
=HLOOKUP(B2,$B$8:$T$9,2)

Drag this formula along row 3 to column T. Cell T3 will contain
=HLOOKUP(T2,$B$8:$T$9,2)

The years shown in the cells of row 2 will change when the slider is moved but, because of this formula, the cell underneath each year will show the correct data. In other words, B8:T9 is static and the range B2:T3 is dynamic.

As Excel 2007 and its add-ins were completely rewritten, what you would do now differs from previous versions. If you have that version, skip down to 'Using Excel 2007'.

Otherwise highlight the range B2 to F3. Select the Chart Wizard to make the chart and start drawing it at about cell B14. In the displayed Source Data dialogue box ensure that the Values are shown as taken from the range $B$3:$F$3 and the X-axis labels from $B$2:$F$2.

To colour the chart background, right-click within the perimeter line and choose, Format Chart Area. Under Patterns Area, choose a colour from the palette. To colour the worksheet, highlight the appropriate range of cells and click the Fill Colour tool on the main formatting toolbar.

To add the slider, on the main View menu choose Toolbars, Forms. Drag this miniature toolbar out of your way. Click on the Scroll Bar control. This offers a cross, indicating a graphic object you can draw. Drag it to create a horizontal box under your chart.

With the left mouse button down, move the little squares on the edges of the object to adjust the size of the new slider.

Right-click on the slider and choose Format Control. In the Cell link box enter
$B$2:$P$2
not $T$2 as you might expect. The Current value and the Minimum value should both be 1994. The Maximum value is 2008, not 2012. The Incremental change is 1, and the Page change is 0. The 3D shading for the slider is optional.

Click OK and your slider is ready. It will shift the X-axis from a 1994 to 1998 view of the chart to a view of the years 2008 to 2012. It will also animate the data: columns on a column chart, and lines on a linear chart, going up and down.

To add the title, ‘Change years’, click on the worksheet. Choose Format cells, Alignment, Text control and merge the cells beneath the slider so you can centre this title.

Finally, on the main Tools menu choose Options. Under the View tab you may wish to eliminate the usual worksheet gridlines.