How do I change the color of the line in a line chart according to value?

[stephenpoole4]

I have a line chart that tracks "change per week."

Some weeks have negative values, some have positive values. Essentially, I want to specify parameters so that negative values are colored green, values near zero (possibly + or - 100) are orange, and positive values are red.

Is there a way to do this? My chart is a PivotChart, if that matters.

 

[Misca]

I don't think you can do that with a line chart without some VBA but without VBA you can get by using a stacked bar chart. You're going to need two value columns: One for the positive values and another one for the (minus) negative values ( = if you want your bars to go the same way you're going to have to show the negative values as positive values as well ). For three colors, you're going to need three columns etc.

 

[Jon Peltier]

1. I wrote a tutorial, Conditional Formatting of Lines in an Excel Line Chart Using VBA, that shows how to do this with VBA.

2. You ought to consider a color scheme besides red and green, since 8% or so of the male population (and less than 0.5% of the female population) has color vision deficiencies that interfere with distinguishing between these colors. Blue and orange are generally color-vision-friendly.