When I switched from Office 2003 to 2010, one of the most liberating updates was the wildly expanded conditional formatting, previously limited to just 3 conditions and some pretty basic cell formatting.
Data bars caught my attention immediately as a great way to visualise a few rows of data quickly without having to make a graph.
One complaint – you’re limited to having all the bars the same colour . . . apparently.
One specific use of these data bars in a cell – as progress bars for tasks/activities in a project – looks pretty lame in monochrome. Wouldn’t it be great to have different colours for each activity based on the percentage of completion? Red for less than 20% (needs attention), orange up to 60%, dark green up to 80% and a really bright green for the final 20%! Oh and must have a completely different colour for 100% – meaning finished and closed.
So I set about seeing how to get from monochrome default to psychedelia.
I make no apologies for writing “colour” – I’m British. 🙂
It MUST be possible to easily work around something so basic, I thought. But looking through forums and generally googling, all I could really find was a post from 2006 by Diego Oppenheimer, who was in the Excel team at the time and now runs Algorithmia. this was before Office 2007 had even been released so we hadn’t even started finding out the good things about data bars and here was one of the developers saying there’s a minus point (but at the same time revealing the workaround):
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)”
So there was the secret. And apparently it was still a secret because nobody seemed to have posted much about it to the forums – no working examples, no clear explanations for the inexperienced – things the Excel communities are generally very good at.
What I got to in the end after a few days of playing around, is a self-contained userform that allows the user to set colours and limits for up to 11 value ranges and then apply all this to the current selection. Why 11? Well as in the example I gave, it all started from the idea of percentages – and ten sub-divisions come naturally – plus that final colour for “completed”. You don’t have to use all 11 and the value range can be anything you want, not just 0 to 100 which was my original need.
Basically it creates 11 formatting conditions for the selection, all of them displaying simultaneously.
The order that the conditions appear in the list is the priority Excel gives them. The trick of Mr. Oppenheimer’s formula is to hide all the colours higher in the list that don’t meet the “>” requirement.
The form works on the current selection so the first step is to select all the cells where you want the same set of conditions to apply. Then show the form (custom button on the ribbon or some object on the worksheet with an assigned macro, for example).
N.B. If you’ve already got some conditional formatting set up for these cells, the form will try to read as much as it can. More importantly, when you click “apply” you will lose all the previous formatting.
Choosing the colours
I incorporated a technique to summon the default colour picker to colour the buttons instead of spending hours coding something. It colours a cell, memorises the value then sets the cell colour back to the original.
So simply click on a button and pick a colour. This is fine for changing a single colour but it’s not the best way to organise a complete matching set. So…
Preset colour themes
During testing I found myself having to set 11 colours and 11 limits every 3 or 4 minutes. An hour of that got me pretty pissed off so I built in some preset colour themes. You can easily add your own too.
Colours in Excel can be represented as a number so the preset colour themes are just arrays of 11 numbers built into the UserForm.
When you select “Light blues” form the preset list, the VBA that gets used is this:
cArray1 = Array(16379861, 16115648, 15916972, 15718553, 15387253, 14990676, 14659377, 13277984, 11108891, 8808213, 6639120)
The colours of the buttons on the form will change to this:
and when applied to a range of numbers, this is the result:
The “fade” buttons
These let you choose just one colour for button/range 1 and the VBA will calculate progressively lighter or darker shades.
Get the cell fill colours from a worksheet
I also found it much quicker to compose sets of colours I liked by colouring cells directly on a worksheet rather than clicking buttons on the form (changing a button colour is three clicks every time). So prepare your range of coloured cells on the same sheet as the range of data where you want to put the data bars. Click “select coloured range”, select the cells, then click “set”.
Set the ranges
You must set the global minimum and maximum limits for the values you want to display as data bars.
At the moment you don’t have all the options of the standard Excel setup – no percentages, percentiles or formulas, no automatic calculation of the highest value in the range, only numbers.
You must also set the maximum value for each range. If you’re using 10 or 11 ranges, you can set the global limits the use the “spread” button to do the work for you.
Mr. Oppenheimer’s example of school grades would be set up like this:
and the result would be
(OK, so I slipped in my special 100% colour again)
Download the Multicoloured Data Bar form:
09/04/2017: download is now here.
I would appreciate comments and criticisms to improve my coding. I plan to incorporate as many of the standard data bar options as possible and any suggestions or requests for additional functions would be welcome.