In the wonderful realm of data-visualization we enjoy the powerful method to encode our data into visual representations, such as points, lines, rectangles – the stuff regular excel charts are made of. Another visual attribute is color. Excel’s conditional formatting engine does a great job in this respect, but only comprises the formatting of worksheet cells. What if we can unleash the power of conditional formatting within our charts?
Like everything Excel, there’s numerous ways to approach the given problem. Robert Mundigl explains three approaches in his excellent and far more comprehensive article Color Coded Bar Charts with Microsoft Excel. But let me share my approach, one that requires just one chart series and no VBA.
Let’s take two data series, one for plotting the chart itself, the other to encode as color. We’ll use an XY scatter plot to create a simple dot plot of the first series. Let’s name the range with the data series data:=Table1. We’ll right away create a variable,
dataLength:=ROWS(data), since we’ll reference its value more than once.
Now, we’re going to exploit the fact that we can over-plot data-points. We format the data markers with a black fill color and a transparency of 90%. Conversely, every over-plot will render the ‘perceived’ marker darker – in reality you’ll be looking at multiple markers on top of each other.
Got it? If not just read on, and see how it’s done. First we’ll create a table that is 10 times the length of the original data series. In the example workbook that’s 10 times 10. We add a simple iterator column
Next, we’ll have to create the X and Y positions of our dot plot. We’ll put the X values in a new column,
j:=MOD(([@i]-1),dataLength)+1, which loops through the length of the original data series . Then we’ll extract the Y values,
values:=INDEX(data,[@j],1) and the color values,
Following, we need to define the thresholds of the 10 color bands. All data in the example workbook is normalized, so the thresholds are easy, running from 0 to 0.9,
Finally, we have to check whether colors exceeds threshold. If so, we’ll return values, if not we’ll return a value of -1,
show:=IF([@color]>[@threshold],[values],-1). We’ll fix the Y axis between 0 and 1, so these negative values will fall outside the plot area, rendering them invisible.
The last step is to set up the XY scatter plot, and format the markers’ fill color transparency to 90%. As a result, the visible markers will stack on top of each other, mimicking a decreasing transparency with every over-plotted marker added to the stack.
Check out the example file:
Obviously there’s a limitation to this technique – we can only encode the level of transparency, not the actual color itself. Implementing multi-color solutions are impossible using over-plotting in just one series.
An interesting line of inquiry would be to experiment whether using multiple, differently colored over-plotted series would mimic this effect. I leave that for another post.
I can see numerous practical applications for this technique. We just need to be mindful of applying color coding in charts, as hue is a ‘guesstimate’ type of property when encoding data.