Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a dataset containing values from 0 to 584. I would like to illustrate this using data bars, but I would like the databars to be green when the number is greater than 150, and red when the number is equal or less than 150.

Consider:

enter image description here

The green bars illustrate the relative values, but I cannot get RED bars on the chart concurrently.

Further consider:

enter image description here

This is what happens when I apply the rule for red to appear if the value is less than or equal to 150. It appears that the red bar illustates relative values from 0 to 150...not 0 to 584.

Is there a way to combine these two rules? Ideally, I would have a data bar for each number that would show it's relative value, but SOME would be green and SOME would be red, based on being > < 150.

Is this doable?

share|improve this question
up vote 3 down vote accepted

You can only apply one data bar rule to a set of cells. You can, however combine data bars and a conditional cell fill based on a formula like

=A1<=150

This will highlight the whole cell, though. enter image description here

share|improve this answer
    
That's what I was afraid of. That was my initial strategy, but I hated the way it looked. :) C'est la vie...thanks for confirmation that I should stop looking for a way to do this. Awesome Excel site by the way...I've got your site bookmarked; I know I'll need some of those chart tips eventually. – dwwilson66 Feb 6 '13 at 11:53

A limit I never liked about data bars was that they are monochrome – one colour for positive values, one colour for negative. So a couple of weeks ago I sat down to figure out a workaround. If anyone’s interested… http://digimac.wordpress.com/2014/06/29/multicoloured-data-bars-in-excel/

share|improve this answer
    
I found out it didn't work except on my PC - a matter of my rather unique language settings which mix English, Italian and German. :-( Fixed and expanded and released as standard AddIn. digimac.wordpress.com/2014/10/10/… Please try it and tell me what you think – DiGiMac Oct 11 '14 at 15:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .