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".

What do we mean by “dashboards”?

The word “dashboard” originally referred to the board at the front of a horse drawn carriage which shielded the occupants from mud spattered (“dashed”) up by the horses’ hooves.

The dashboard was retained in horseless carriages. It became a convenient place to fix instruments so developed a new meaning as an alternative to “instrument panel”

The word “dashboard” was adopted by business information specialists somewhere around the early 1990s through the analogy between a car dashboard and a collection of “controls” for monitoring information.

The information use of the term “dashboard” became increasingly widespread from around the late 1990s. Many system suppliers took the car analogy a little too literally. Developers tried to recreate car dashboard visual characteristics in information dashboard products. Some of these products were visually stunning whilst at the same time being relatively ineffective for their supposed primary purpose.

Such excesses have been reeled back in slightly by the efforts of visualization gurus such as Edward Tufte and Stephen Few

We are now at a point where widespread over-use of the word “dashboard” has almost stripped it of any meaning

Use of terminology varies but the following concepts can be distinguished:

A single screen (or page of paper) which summarises at a glance the current position of one or more key measures for an organization or enterprise: I call this a dashboard

A more extensive collation, perhaps showing trends or a detailed breakdown of one or more key measures for an organisation or enterprise, and quite likely including commentary: I call this a report

A collection of the key indicators for an organisation, sub-organisation or enterprise: I call this an indictor set

One or more overall scores can be collated to provide a summary of an indicator set: I call this a scorecard  (the term “balanced scorecard” is widely used but for me to agree to use it I would need to have it explained how the balancing has been carried out)

A dashboard or report in which the user can obtain different versions of the information by entering choices: I call this an interactive dashboard or an interactive report