Animate your Excel charts

Using Excel 2007
If a Developer tab is not showing above the ribbon, click on the Office icon and then the Excel Options button. Under Popular, Top options, check the box next to ‘Show Developer tab in the Ribbon’.

The data table is set up exactly as for previous versions, with an exception. Enter a label in cell A3 and make sure cell A2 is empty. Excel has a new feature called Intellisense which gets confused if you don’t do this. The label used in the example is ‘Acres’.

Highlight the range A2:F3. If you want the chart to be created on a new worksheet press F11. But it’s much easier to add the slider if you display the chart on the same sheet as the data, so press Alt & F1. This displays a basic chart with Acres for a Legend and a Title and a new Chart Tools tab, with a Design tab below.

This simple chart doesn’t need both a title and a legend so right-click on the Legend box and choose Delete.

To format the columns, right-click on a column and choose Format Data Series, 3D Format, Bevel, Top, 6pt width and height. Also Fill, Solid Fill, Green.

Click on the Chart Area, choose Chart Tools, Format, Shape Fill, More Gradients, Gradient fill, Type: Linear, Angle: 70 degrees.

Right-click on a number in the vertical Y-axis and a little font formatting toolbar appears. Using this you can change the font, its size, and colour. The same applies to the horizontal X axis and the title, all of which you will probably prefer bigger and bolder than the default.

To add the slider, under the Developer tab, choose Insert on the ribbon. In the bottom right-corner of the ActiveX Controls is a crossed spanner and hammer leading to a long list of controls. Pick Microsoft Forms 2.0 Scroll Bar. A little cross appears. Draw this along under the chart to create the slider. In the Formula Bar =EMBED("Forms.ScrollBar.1","") appears. This confirms you have the right control, although the slider is rather pale and needs reformatting.

You’ll notice that next to the Insert icon, under the Developer tab, is an icon labelled ‘Design Mode’. Whenever this is lit up you can format the slider. To the right of the Design Mode icon there is an option labelled Properties. Click that to display the Scrollbar properties dialogue box.

Click the Alphabetic tab. Click beside BackColor. A down arrow appears. Click the Palette tab and choose a colour for the slider to match or complement the colouring of your chart. Similarly pick ForeColor, Palette, and probably White, as this refers to the pointers at each end of the slider.

Beside LinkedCell enter
$B$2:$P$2

Beside Max put 2008 or 2009, depending on your chart’s size. Min and Value are both 1994. Close this dialogue box. To get out of Design Mode so you can operate the slider, click the Design Mode icon and its light will go out. You can now change the view of the chart either by dragging the slider or clicking the arrow at either end.

To be helpful to other users you can add an instructional caption under the slider. Enter ‘Change years’ in a cell to the left, below the slider. Highlight this cell and several to the right. Right-click and choose, Format Cells, Alignment, and check the Merge cells box under Text control. Under Text alignment, Horizontal choose, Centre.

If you want to remove the worksheet gridlines around the chart and slider, click the Office icon and choose Excel Options, Advanced. Under ‘Display options for this worksheet’ remove the checkmark against Show gridlines.

Creative formatting
The options for formatting Excel charts are enormous. The limitations for turning them into superb graphics are not dictated so much by the software as by the capabilities of the artist.

The largest area of the chart, the background if you like, is logically refe rred to as the Chart Area. This can be a solid colour, pattern, or like a material – marble, hessian, or crumpled paper, for instance. The weight of these can be graduated across the area, from light in one corner to heavy in the opposite one. The Chart Area can be a clip art graphic or one of your own photos. These can be shown in one piece or tiled across the area.

Within the ChartArea is the Plot Area. This offers all the same options, and it can be a window which just shows the Chart Area picture through it.

A column – or any other shape used to mark data points – can similarly be an appropriate piece of clipart or a photo, in one piece or with miniature versions of it stacked up the column.

The width, colour and even texture of Major and Minor Gridlines can be varied. They can be dots or dashes, single or double, and have arrows at each end.

The vertical and horizontal axis font can be any one of the dozens offered in Office, at any size, colour or weight of your choosing. And you can set minimums and maximums for them so that they don’t have to start from zero.