Bandline Chart Designer

Bandlines were invented by Stephen Few as an extension to Edward Tufte’s Sparklines. They are intended to bring context to a column of Sparklines by giving additional information about the scale or the numbers without the need to use a shared scale, which in many cases will totally flatten out the trendline for the smaller series. More detailed information on the design goals and background is available here [1].

In XLCubed bandlines are implemented as a background option within the Sparkline Chart Designer.

To add Bandlines to an existing Sparkline, edit the Sparkline to format it and go to the Background tab, then turn on ‘Banded Fill’.

Bandlines are intended to show the spread of data across a defined set, and exactly how this is displayed can be defined by the user. The user can set the background colour for Quartile 1, the Midrange (Q2 & Q3), and Quartile 4, along with the colour of the Median line.

There are several approaches to displaying the band information:

• Use the data set being plotted to define the quartile bands (the default)

In this case if you are plotting 28 days data, the trendline itself will be based on the individual row of data, but the banding detail is worked out from the entire set of data being plotted.

• Use an extended / alternate range to define the quartile bands

For example, it may be useful to look at the current month trendline set in the context of the previous 12 months. Here ‘Data for bands’ must be checked and the appropriate range of data selected. This is on a row by row basis (i.e. the range selected for the banding must have the same number of rows as the range for the trendline) and the bands vary row by row.

• Use Custom Percentile Boundaries

Rather than use quartiles, it’s possible to define custom percentile bands. This approach still requires one row of incoming data, specifying 5 ascending percentage values (typically starting with 0% and ending with 100%).

• Include Band data in Scale

There will be higher and lower values in the set of data being used to calculate the bands than in most individual rows being plotted. The dialogs allow you to choose whether or not to include the defined bands in the scale. Including the banding data will have the effect of flattening the trendline, so where the overall values being plotted are fairly close, it may make sense to do include this. However where there is significant variance it is generally best not to include it, and to rely on the colour banding to display this information.

The example below shows the potential difference in this approach, where the Sparkline itself shows the previous 30 days volumes, and the bands are based on the previous 90 days. Where the bands are included in the scale the trendlines are much flatter, and everything is on a consistent scale. With Bandlines excluded from the scale here excluded the individual trends are more obvious, and their position in the overall spread can still be determined from the background colour of the associated quartile the values are falling into.