3

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?

Improve this question

2 Answers 2

Reset to default
3

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

Improve this answer
1
  • 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, 2013 at 11:53
0

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/

Improve this answer
1
  • 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, 2014 at 15:23

You must log in to answer this question.

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