Doubled bar chart labels in Excel

The following bar chart was published on the BBC website

http://www.bbc.co.uk/news/world-us-canada-41479011

Not withstanding the somewhat gruesome subject matter, my interest was caught by the labelling of the categories. The labels had both the location and the year, with the year in bold and on a second line.

I wondered whether it was possible to achieve this effect using Excel

Forcing the year onto a fresh line can be achieved by inserting a "hard" carriage return. (ALT and ENTER keys at the same time)

Jon Peltier provides a method of adding and then formatting the category labels using a second (dummy) data series. https://peltiertech.com/individual-format-category-axis-labels/. The alignment of the labels can be set to right




I have not yet found a way to selectively apply Bold formatting to the text in the label

Creating a Fan Chart in Excel

Mary Eleanor Spears provides an example of a ‘Fan Chart’ in her book 'Charting Statistics’ (McGraw Hill Book Company. 1952):

The Fan Chart is in effect a variation of the Slope Chart. It shows relative change rather than absolute change. So all points on the Left Hand side start on zero.

A version can be produced relatively easily in Excel:

This version was made from the following starting data:

The main steps were:

Create a basic line chart from the data
Select Data Source: Switch Row/Column
Delete legend
Delete header
X Axis: set  label position high
Format each data series line colour and thickness
Format Y axis tick marks and line
Format Y axis so that X axis crosses at zero
Format Y axis maximum value to 10
Delete all gridlines
Set X axis position on tick marks
Add data labels (to right)
Delete all LH data labels (carefully so as not to delete RH at same time)
Format each RH label with

• Series name not value
• No leader lines
• Position to Right side

One way to add the shading outside of the data range is to create a second chart and overlay the fan chart. Create a clone of the chart and turn that into an area chart, being careful not to change the size at all. Turn the plot area of the clone grey and turn the fill of the wedges within the data area white

Then copy the line version of the fan chart and position it over the area version (making the top chart see-through by use of 'No Fill') settings). Here is a screenshot of the effect:

Slope Graphs

The slope graph - or slope chart- is a special type of line chart. It is particularly useful for showing multiple  ‘before and after’ comparisons. It may be either based on rankings or on actual scores.

The slope graph is often said to have been devised by Edward Tufte (he published an example in his ground breaking 1983 book 'The Visual Display of Quantitative Information'). Tufte developed and promoted the idea but did not 'invent' it as such.

A slope graph was published in another book on the visual display of quantitative Information,  69 years before Tufte:

(part of Figure 63, p.65, from 'Graphic Methods for Presenting Facts' 1914 by Willard C Brinton - revised edition 1919)

Another example, more modern and also pre-Tufte, was published by Mary Eleanor Spears in her book 'Practical Charting Techniques'.(McGraw Hill. 1969). She describes it as a Ranking Chart:

Unlike the busy 1914 example, focusing on the change between just two points allows us to read easily. The gradient of the connecting line quickly gives both direction and magnitude. This is a considerable advantage over illustrating with a simple table

Edward Tufte has posted more examples on his website: https://www.edwardtufte.com/bboard/q-and-a-fetch-msg?msg_id=0003nk&topic_id=1 including possibly the earliest ever slope graph (from  Scribner's Statistical Atlas of the United States,1883)

Both Jon Peltier (https://peltiertech.com/slope-graphs-in-excel/) and Stephanie Evergreen (http://stephanieevergreen.com/slopegraph/) have provided step by step details of how to create a slope chart in Excel. The latter is clearer, and is also covered her book 'Effective Data Visualization: The Right Chart for the Right Data'.

Using these instructions, together with a tip from Chandoo, enables us to create an Excel version of Mary Eleanor Spears 1960s original:

Producing this chart in Excel was slow, but not difficult. The labels on the left-hand side were formatted using Stephanie Evergreen's method (making Series and Value visible then omitting the comma between the two).


The following data layout was used to create the Slope Chart in Excel:

Column D is used solely for the Right-Hand labels. It is not included in the ‘Select Data’ range

I could not find a way to reverse the order of Value and Series using Stephanie Evergreen's method. Chandoo pointed out that it is possible to set the value of individual labels using a formula. So, the solution for the right-hand labels is to create the label in another spreadsheet cell and then point each label to the specific cell

This method is quicker than the Evergreen method. Unfortunately, it does not work for the Left-Hand labels – if you want to reuse the chart with refreshed data - as the order is volatile.

A final step might be to format one feature a different colour or intensity to highlight it.

The Slope Chart allows a lot more series to be compared in a single chart than would be sensible with a regular line chart. But what is the upper limit for a Slope Chart? That will depend on the way that you want to use it. Take the following example which compares 44 series in a single chart:  

 From a conventional dashboard perspective, this is now too much to fit with ease on a single screen. So, it will not work easily in an Excel dashboard or PowerPoint slide. But it still works if printed on paper. And it could work in a single screen if set up with a window to roll over the full chart, displaying a portion at a time.

Another approach would be to simplify by removing the labelling