Adding colours to the Dot Chart

In this blog we build on the original blog showing you how to make a horizontal dot chart which can be very useful for showing % complete for projects across a programme in a simple chart to add colour which can be used to show status or RAG.

I often use this when communicating either risk or the RAG (Red, Amber, Green) status of projects within a programme.  This is especially useful in the run up to a Go Live even and I want to communicate how ready different workstreams are for the event.

To do this we need to add more data series to the chart which will carry the different colours.  Calculations will then decide which series each data point should be part of.

First we need to set up the data;  add additional columns to the original data set.  

The first should be a "decision" column so that you can easily see which series the data point should be part of.  Strictly you don't need this as you could include the decision in each of the data series however it makes reading and trouble shooting the data a lot easier.  In this case we have "Band" - should the data point be Red, Amber, Green or complete.

Based on the entry in the decision column the series will either show NA() which is an error and so can't be plotted or show the normal height calculation which is at the heart of the horizontal dot chart.

In this case the calculations are;

Decision: C2 =IF(B2=1,"complete",IF(B2<=$B$11,"Red",IF(B2<=$B$12,"Amber","Green")))

Red: D2 =IF($C2=D$1,(ROWS('Dot chart'!$B$2:$B$8)-ROW()+ROW('Dot chart'!$B$2)-0.5)/ROWS('Dot chart'!$B$2:$B$8),NA())

Complete G2 =IF(B2=1,(ROWS('Dot chart'!$B$2:$B$8)-ROW()+ROW('Dot chart'!$B$2)-0.5)/ROWS('Dot chart'!$B$2:$B$8),NA())

Copy C2 down to C8

Copy D2 across and down to F8

Copy G2 to G8

Next we need to build the chart as for the simple horizontal dot chart with some minor changes: 

1. Use A1:B8 to chart the horizontal bar chart
2. Edit the Y axis
3. Select B1:B8 and then hold down the <CTRL> key whilst selecting D1:D8
4. Copy and use Paste Special onto the chart and then change the new data sereis to XY Scatter
5. Repeat for the other data series.  Note that it is important to ensure that you have at least 1 of every data series (i.e. the series can't consist entirely of errors).  To do this fake one of the decisions; you can always copy the formula from C2 to get rid of the fake result later.
6. You can now select each series and format it to give the different coloured dots..
7. Hide the axis etc

Now you have a chart which shows the status of each dot.

Note that on this chart I used the ability to replace markers with a picture of your choice, as shown in this blog post, to replace the markers for the completed series with a tick.