Conditional Formating - dynamic data bar colors
 

Hi,

I am working on dashboard type tracking list. I am using the conditional
formatting Data Bars to show progress based on a 1-10 number scale. I am
using just a simple grey color for the actual data bar.

A friend asked if I could add a color scheme to the data bar such that it
changes depending upon the actual number in the cell:

1-3 = Data bar is Red
4-6 = Data bar is Yellow
7-10 = Data bar is Green

any suggestions?

thanks,

Answer: Conditional Formating - dynamic data bar colors
 

Adding Color Scheme to Data Bars in Excel Using Conditional Formatting

1. Select the cells that contain the data bars you want to format.
2. Click on the "Conditional Formatting" button in the "Home" tab of the ribbon.
3. Select "New Rule" from the drop-down menu.
4. In the "New Formatting Rule" dialog box, select "Format only cells that contain" from the "Select a Rule Type" section.
5. In the "Edit the Rule Description" section, choose "Cell Value" from the first drop-down menu, "between" from the second drop-down menu, and enter "1" and "3" in the two boxes.
6. Click on the "Format" button and select the "Fill" tab.
7. Choose the red color you want to use for the data bars and click "OK".
8. Click "OK" again to close the "New Formatting Rule" dialog box.
9. Repeat steps 4-8 for the other two ranges (4-6 and 7-10), using yellow and green colors respectively.

Once you've set up these rules, the data bars in your selected cells will change color based on the value in the cell. If the value is between 1 and 3, the data bar will be red. If it's between 4 and 6, it will be yellow, and if it's between 7 and 10, it will be green.

Conditional Formating - dynamic data bar colors
 

"Sirpent0r" wrote in message
...
Hi,

I am working on dashboard type tracking list. I am using the conditional
formatting Data Bars to show progress based on a 1-10 number scale. I am
using just a simple grey color for the actual data bar.

A friend asked if I could add a color scheme to the data bar such that it
changes depending upon the actual number in the cell:

1-3 = Data bar is Red
4-6 = Data bar is Yellow
7-10 = Data bar is Green

any suggestions?

thanks,

Just use conditional formatting 'Cell value is between 1 and 3' etc and
chose the colour via Format, Patterns.

Then add the next condition and repeat. Repeat again for the third
condition.

V

Conditional Formating - dynamic data bar colors
 

Hi Victor,

oh i should have stressed that I want to maintain the overall 1-10 scale.
Meaning that as the bar fills the 1st 3rd of the cell it is red, then changes
to yellow as the bar continues to fill the cell the next 3rd of the way, then
changes to green as it continues to fill the last 3rd of the way.

I could not find a way to do this just using the conditional formatting
options from the menu.


"Victor Delta" wrote:

"Sirpent0r" wrote in message
...
Hi,

I am working on dashboard type tracking list. I am using the conditional
formatting Data Bars to show progress based on a 1-10 number scale. I am
using just a simple grey color for the actual data bar.

A friend asked if I could add a color scheme to the data bar such that it
changes depending upon the actual number in the cell:

1-3 = Data bar is Red
4-6 = Data bar is Yellow
7-10 = Data bar is Green

any suggestions?

thanks,

Just use conditional formatting 'Cell value is between 1 and 3' etc and
chose the colour via Format, Patterns.

Then add the next condition and repeat. Repeat again for the third
condition.

V

Conditional Formating - dynamic data bar colors
 

I don't think you can do that. The color gradient options show the
range of colors to be displayed based on a cell's value, but the cell
cannot actually show more than a single color.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Thu, 3 Sep 2009 15:24:02 -0700, Sirpent0r
wrote:

Hi Victor,

oh i should have stressed that I want to maintain the overall 1-10 scale.
Meaning that as the bar fills the 1st 3rd of the cell it is red, then changes
to yellow as the bar continues to fill the cell the next 3rd of the way, then
changes to green as it continues to fill the last 3rd of the way.

I could not find a way to do this just using the conditional formatting
options from the menu.


"Victor Delta" wrote:

"Sirpent0r" wrote in message
...
Hi,

I am working on dashboard type tracking list. I am using the conditional
formatting Data Bars to show progress based on a 1-10 number scale. I am
using just a simple grey color for the actual data bar.

A friend asked if I could add a color scheme to the data bar such that it
changes depending upon the actual number in the cell:

1-3 = Data bar is Red
4-6 = Data bar is Yellow
7-10 = Data bar is Green

any suggestions?

thanks,

Just use conditional formatting 'Cell value is between 1 and 3' etc and
chose the colour via Format, Patterns.

Then add the next condition and repeat. Repeat again for the third
condition.

V

Conditional Formating - dynamic data bar colors
 

Thanks Chip,

Yea i could not see a way to do this either. I was wondering if there were a
way to apply a formula that could manipulate the data bar properties, such as
a "If / Then" type statement.

If cell value = 1,2, or 3, then data bar gradient = Red
If cell value = 4, 5, or 6, then data bar gradient = Yellow

something like that?




"Chip Pearson" wrote:


I don't think you can do that. The color gradient options show the
range of colors to be displayed based on a cell's value, but the cell
cannot actually show more than a single color.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Thu, 3 Sep 2009 15:24:02 -0700, Sirpent0r
wrote:

Hi Victor,

oh i should have stressed that I want to maintain the overall 1-10 scale.
Meaning that as the bar fills the 1st 3rd of the cell it is red, then changes
to yellow as the bar continues to fill the cell the next 3rd of the way, then
changes to green as it continues to fill the last 3rd of the way.

I could not find a way to do this just using the conditional formatting
options from the menu.


"Victor Delta" wrote:

"Sirpent0r" wrote in message
...
Hi,

I am working on dashboard type tracking list. I am using the conditional
formatting Data Bars to show progress based on a 1-10 number scale. I am
using just a simple grey color for the actual data bar.

A friend asked if I could add a color scheme to the data bar such that it
changes depending upon the actual number in the cell:

1-3 = Data bar is Red
4-6 = Data bar is Yellow
7-10 = Data bar is Green

any suggestions?

thanks,

Just use conditional formatting 'Cell value is between 1 and 3' etc and
chose the colour via Format, Patterns.

Then add the next condition and repeat. Repeat again for the third
condition.

V

Conditional Formating - dynamic data bar colors
 

"Sirpent0r" wrote in message
...
Thanks Chip,

Yea i could not see a way to do this either. I was wondering if there were
a
way to apply a formula that could manipulate the data bar properties, such
as
a "If / Then" type statement.

If cell value = 1,2, or 3, then data bar gradient = Red
If cell value = 4, 5, or 6, then data bar gradient = Yellow

something like that?

Why not make the data bar out of 10 separate cells? Then it could be done...

V

Conditional Formating - dynamic data bar colors
 

"Sirpent0r" wrote in message
...
Thanks Chip,

Yea i could not see a way to do this either. I was wondering if there were
a
way to apply a formula that could manipulate the data bar properties, such
as
a "If / Then" type statement.

If cell value = 1,2, or 3, then data bar gradient = Red
If cell value = 4, 5, or 6, then data bar gradient = Yellow

something like that?

Why not make the data bar out of 10 separate cells? Then it could be done...

V

Conditional Formating - dynamic data bar colors
 

i'm not quite sure i follow that... can you elaborate?


"Victor Delta" wrote:

"Sirpent0r" wrote in message
...
Thanks Chip,

Yea i could not see a way to do this either. I was wondering if there were
a
way to apply a formula that could manipulate the data bar properties, such
as
a "If / Then" type statement.

If cell value = 1,2, or 3, then data bar gradient = Red
If cell value = 4, 5, or 6, then data bar gradient = Yellow

something like that?

Why not make the data bar out of 10 separate cells? Then it could be done...

V

Conditional Formating - dynamic data bar colors
 

"Sirpent0r" wrote in message
...
i'm not quite sure i follow that... can you elaborate?

Create your data bar out of 10 narrow cells (or three if you want to go for
thirds, as you mentioned earlier on).

Surely, you can then set the conditional formatting for each cell to change
in a way that creates the effect you described.

V

Conditional Formating - dynamic data bar colors
 

I've done this myself. I'm having another problem with conditional formating
though which I will post below.

To do the bar do this:

Make 3 (or more) columns next to each other the size you want each increment
to be. I was passing a percentage, that is, I had a cell somwhere else that
showed between 0% and 100%, I wanted a bar that would show red for 0-33,
yellow for 34-64, and green for 65-100 percent.

So, I had this percentage report to EACH the three cells next to each other.
I aligned the text in the cells to the right and I made three conditions as
follows:


If Cell 1 is between 0 and .33, make it red and the text black
If Cell 1 is between .34 and .64, make it yellow and make the text yellow
If Cell 1 is between .65 and 1.0, make it green and the text green

If Cell 2 is between 0 and .33, make it white and the text white
If Cell 2 is between .34 and .64, make it yellow and make the text black
If Cell 2 is between .65 and 1.0, make it green and the text green

If Cell 3 is between 0 and .33, make it white and the text white
If Cell 3 is between .34 and .64, make it yellow and make the text yellow
If Cell 3 is between .65 and 1.0, make it green and the text black

This will leave you with a bar that starting from the left is red, yellow,
or green with the value showing on the right most side of the bar...I hope
this helps, it's a little tought to explain.

Now my question, I have a column that has cells that say "Incomplete" (with
red formating) and change to "complete" (green formating) when a value I look
up is met, say if a cell is 0 somewhere else, it's incomplete, and if it's 1,
it's complete. Once it turns to complete, I want it to stay that way (until
next time I load the workbook) even if the cell I'm looking at turns back to
0. Any ideas?

"Victor Delta" wrote:

"Sirpent0r" wrote in message
...
Thanks Chip,

Yea i could not see a way to do this either. I was wondering if there were
a
way to apply a formula that could manipulate the data bar properties, such
as
a "If / Then" type statement.

If cell value = 1,2, or 3, then data bar gradient = Red
If cell value = 4, 5, or 6, then data bar gradient = Yellow

something like that?

Why not make the data bar out of 10 separate cells? Then it could be done...

V