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