I usually like the BBC's data visualisations - but then this happened...

The BBC website generally provides excellent examples of how to present information. I have learnt a lot from studying material produced by the BBC. So imagine my disappointment when I encountered the following today  ( http://www.bbc.co.uk/sport/formula1/36671922):

The first issue is why the BBC felt that it was necessary to have a chart at all. There are three drivers having won 5, 4 and 3 times respectively. A simple table, such as the following, would have conveyed this information more clearly

Aside form the poor use of font weight, the BBC chart has managed to get the drivers' names to align vertically, making them hard to read. Choosing a horizontal bar chart, rather than a vertical column chart, would have avoided this. For example:

I think the graph above is entirely clear. But if the BBC needed to add some more visual interest, they could have added further embellishments,  such as using a pictograph:

This is quite easy to do with Excel. The example above took about ten minutes, most of which was creating a suitable image file. With a  little more time and care it could be made much more aesthetically pleasing, although for some reason these types of graphs always make me think of the 1950s.

Perhaps later today the BBC may update their graphic - maybe on aesthetic grounds - but preferably also because the data will have changed!

Creating non-standard charts in Excel: shadow bars

There are types of chart which are not offered as part of Excel's standard library but which can be produced with a little imagination and perseverance, This short article explains how to produce  a "shadow bar" chart such as in the following illustration:

In dashboards, this type of chart might be useful for summarising a "balanced scorecard" where individual indicators are grouped under thematic headings. Current performance is shown in the RAG total bars. Muted colours are then used to show the previous values so that relative change can be gauged quickly

Although not offered as part of the standard set of templates, this type of graph can be produced in Excel. One way to is to produce two separate charts, make one transparent and position it carefully over the other.


Step-by-step 

Set out the data (noting the layout of rows and columns):

Highlight the first block ("Latest") and insert  100% Stacked Bar graph . Depending on Excel version and settings, this will produce something like the following (Excel 2016):

Make the following changes (exactly how will vary between Excel versions):

• Get rid of the Chart Title 
• Get rid of the Legend
• Get rid of Horizontal Axis Labels
• Get rid of the Horizontal Axis Gridlines
• Get rid of Chart Border line
• Increase the font size of the series labels
• Format Data Series Colours to Red,Orange and Green to better reflect RAG

You will have produced something similar to the following:

Now create a copy of this entire graph. This will be used for the "Previous" shadow values. Working on the copy, make the following changes:

• Change the Data Source to the alternate data set
• Format Data Series Colours to muted shadow colours (greys have been used here)
• Format the Colour of the Series labels to White (or whatever other background colour)

You will have produced something similar to the following:

Next, change the Chart Area of the original ("Current") graph to No Fill. It can now be positioned over the top of the shadow graph and the positions fine tuned to given the desired effect.

 

Various refinements and embellishments can be added but beware of making the final product too "busy".