Conditional Formatting Trick 1 – Multi-Coloured Data Bars
A few months ago, I described the new features we have added to Excel 2007 in the area of conditional formatting. One of the new formats we added is called a “data bar” … check out this earlier post for a refresher, but the basic idea is that Excel draws a bar in each cell representing the value of that cell relative to the other cells in the selected range. Here is a shot from that post.
(Click to enlarge)
The Excel 2007 UI allows you to choose whatever colour you want for your data bars, but, by default, all the data bars you apply to a range have to be the same colour. Someone on our team recently showed me how to use a tiny bit of VBA to simulate having multiple colours of data bars on a range conditionally applied, so I thought I would pass along the trick.
Say, for example, you are looking at student grades, and you want all the data bars for students with a passing mark (60%+ and above) to be green, and those with a failing grade (59% or less) to be red. The first thing you would do is to add some red data bars to your data, and then some green data bars. By default, Excel shows you the last set applied, so the data bars would be green. If you then launch the VB Editor (Alt + F11) and in the immediate window (Ctrl+G), type:
selection.FormatConditions(1).formula = "=if(c3>59, true, false)"
You would see that your data now looks like this, which makes it easy to spot the failing grades.
(Click to enlarge)
So how does this work? Every conditional format has a Formula property, which allows you to specify a formula which determines whether the conditional format is visible. In this case, we are simply saying that the green data bars (the most recent ones) should only be visible if a value is greater than 59.
This property is available on all conditional formats, so I expect that users will find all sorts of creative uses beyond just this case.
