Multicoloured Data Bars in Excel

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.

2014-06-29_144538          2014-06-29_144316

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.

2014-06-29_143551

Basically it creates 11 formatting conditions for the selection, all of them displaying simultaneously.

2014-06-29_144818

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

2014-06-29_143807  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.

2014-06-29_143838

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:

2014-07-08_090253

and when applied to a range of numbers, this is the result:

2014-07-08_090742

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.

2014-07-08_103758

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.

2014-07-08_120352

2014-07-08_120426

Mr. Oppenheimer’s example of school grades would be set up like this:

2014-06-29_165729

and the result would be

2014-06-29_165436

(OK, so I slipped in my special 100% colour again)

 

Download the Multicoloured Data Bar form: ColourBars

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.

Annunci

Informazioni su DiGiMac

I'm an ex-pat Londoner. I've been living near Lake Garda in North Italy for more than 30 years.
Questa voce è stata pubblicata in English e contrassegnata con , . Contrassegna il permalink.

12 risposte a Multicoloured Data Bars in Excel

  1. Metfan ha detto:

    Hey DigiMac,
    I’ve been looking for a code like this for a while now so i really appreciate you sharing. I was wondering if you could explain how to use the program once we have downloaded the dropbox link. I am having trouble opening the two files. I would assume I need to open it in VBA- however, it isn’t working as well as I was hoping. Could you explain this?
    Thanks,
    Metfan

  2. DiGiMac ha detto:

    Hi Metfan,
    I MUST put up a proper page for the files with more explanations and samples. I will also put up the latest version. Probably this evening…
    But quick n dirty, you have to open a VBA project and import the form.

  3. acbyram ha detto:

    Hi DiGiMac

    Thanks for writing such a great tool. Much needed!

    Maybe I’m missing something, but I can’t get it to work when the min-max range is [0,1] and the cell values are like 0.5, but seems to work fine when the range is like [0,25] and the cell values are integer.

    Thanks again
    Adrian

  4. Ash83 ha detto:

    Hi

    This is exactly what I have been looking for the past few days, but i’m not having any luck in using your add-in, will need a few pointers.

    I need a data bar that is in percentage, anything below 80% is red and anything above 80% is Green.
    I also need below 60% red, between 60-79% amber and above 80% is green.

    How do I do this, im getting runtime error 13 & 11.

    Thank you

  5. Nelema ha detto:

    I was using your work and it seems like such a useful task but I can’t seem to get it to work? I tried doing your last example following exactly the options you have chosen however the data bars are only coloured in the first colour (red). Green and amber do not appear at all and I don’t know what I’m doing wrong. Would appreciate the help!

  6. Vineet ha detto:

    Hi, Sounds like great work, but am unable to open the file after downloading. Says the Zip archive is corrupt. Could you please check? Thanks in Advance.

    • DiGiMac ha detto:

      Hi, I’m sorry Vineet and everyone else who has visited and tried to download the zip.
      I’ve substituted the file on Dropbox and updated the link on both pages. Hopefully now you’ll be able to try it out.
      I’m ashamed of how I’ve neglected the site as I know how annoying it is to read about something and think it sounds great then you discover the link is broken or corrupt or just doesn’t work.
      I have only tested it in Excel 2010 under Windows 7 but I’ll do everything I can to solve any problems that arise

  7. HI,

    can you tell me where the macro or vbscript is that sets the upper bound and lower bound values for the conditional formatting? I’d like to modify the script so the upper bound is a variable set by a cell in the workbook.

  8. 강남백마 ha detto:

    Because the admin of this site is working, no question very rapidly
    it will be famous, due to its feature contents.

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...