We now return to conditional formatting - what’s a colour scale?
The second new visualization that we have added to Excel 12 is something we are calling “colour scales” (again, that may change later when we finish official feature naming). It shares a lot with data bars as described in a previous post – it is a comparison between a selected range of cells, it uses visual effects to communicate the results to users, and it is just as configurable as a data bar with respect to setting colour and thresholds. So how is a colour scale different from a data bar? A colour scale uses cell shading, not bars drawn in the cells, to communicate relative values. This is extremely useful when you want to communicate something else about your data beyond the relative size of the value of a cell – for example, if high numbers are good and low numbers are bad, or if you want to understand variation in your data, colour scales are a great choice.
Let’s look at an example. Say you have some data about investment returns (note that all this data is completely pretend stuff I just typed in myself for illustrative purposes):
(Click to enlarge)
If you select that range and apply a colour scale, the range now looks like this:
(Click to enlarge)
It is now easy to see good returns, weaker returns, trends, outliers, etc., where red is bad, green is good, and yellow is in the middle. This works in a similar fashion to data bars - Excel is comparing the values in each of the selected cells and assigning a background colour based on a cell’s value relative to all other selected cells. The colours provide clear information to users. By default, when you apply colour scales with one click, Excel uses the lowest, highest, and midpoint values in the range to determine the colour gradients. As with data bars, you can specify the values that determine what colours to use – you can tell Excel to use the lowest/highest/middle value, or, for each of the colours, you can specify a number, percent, percentile, or formula. Here is a shot of the (not final) UI to change settings on colour scales:
(Click to see list)
Colour scales are a good example of why it is great to have Excel 12 support 32-bit colour – you can see that we have an almost infinite ability to vary the background colour of a cell between red, yellow, and green (or whatever colours you choose). Excel 12 will offer colour scales that support 2 and 3 different colours. Since we have already seen a 3-colour colour scale, let’s finish the post with a picture of a 2-colour colour scale. In this example, in one case we are moving from green to yellow, and in the second case I have set the lower colour to be white, so it appears to the user that the intensity of the blue colour increases as the value of a cell increases.
(Click to enlarge)
Hope this is interesting information. Next time, icon sets.
