In Invert Excel Chart Format if Negative I showed the technique that allows you to select which colors for positive and negative points in a column or bar chart. This technique works in Excel versions up to and including Excel 2003, but doesn’t work in Excel 2007. Up until now I thought you couldn’t get two colors of your own choosing in Excel 2007 unless you used two series, or colored each point manually or programmatically based on its value.
Well, I was wrong. Invert if Negative can be used with any two arbitrary colors in Excel 2007. You just need to know the trick.
In my Area Chart – Invert if Negative post, I went through a convoluted protocol to color an area chart differently for positive and negative areas. In a comment under that post, alert reader Vincent wrote the following, speaking of column and bar charts:
I have been looking for an option in Excel 2007 to do the invert if negative option just like I did in 2003. On every website it states that it cannot be done unless you use two series. I couldn’t.
Then I found this:
http://www.hichert.com/de/community/foren?func=view&catid=6&id=150It works! And I like to share this with all of you looking for this solution!
The protocol uses Excel 2007′s gradient fill formatting of the bars, with an unusual and nonintuitive gradient definition. This tutorial is a reworking of the technique described by Andrej Lapajne in the citation above.
The Chart
Let’s start with a simple column chart using arbitrary positive and negative values. Never mind for this exercise the category labels located in the negative bars.
Invert if Negative in Excel 2007 Dialog
We can invoke Invert if Negative using the checkbox in the Fill panel of the Format Data Series dialog in Excel 2007.
What we get is the same as the initial Invert if Negative chart in Excel 2003: the positive bars stay the same, the negative bars are filled with white. Excel 2007 adds the border, otherwise the bars would not be visible. The technique that adds a second color in Excel 2003 fails in Excel 2007.
Gradient Fill Formats
In general, applying a gradient fill pattern in a chart adds that special kind of chart junk that attracts attention while obscuring the data. However, this technique requires the gradient feature, though by the end there will be no gradient, just two colors of bars.
Let’s make a simple gradient, defined by two “stops”, that is, the colors at the two endpoints. Select the Gradient Fill option, then set Stop 1 at position 0% to show the first color, green for positive.
Set Stop 2 at position 100% with the second color, red for negative.
What we get is the default gradient, changing gradually from green to red. Invert if Negative has been checked, so the positive and negative bars show the gradient in opposite directions.
The Magical Gradient
I have to admit that I do not understand why this magical gradient works. If I did, I probably wouldn’t call it magical. Mr. Lapajne is either a genius or a madman, or perhaps he swiped the gradient from another genius or madman like I’ve swiped it from him. In any case, the gradient requires four stops, described below:
Where do the 1% and 99% stops come from? I don’t know, and it must have taken a madman or someone with finely honed obsessive-compulsive disorder to have determined them. For “fun”, I experimented with a wide variety of similar and different settings, and I would never have found the correct ones, even by accident.
The result is just what we hoped for, green bars for positive values, red for negative values.
Why Haven’t I Been Writing?
Good question. I’ve been busy and distracted.
Last week I had the honor of addressing the 5th annual Excel Experience Day, sponsored by Dutch publishing and information services provider Kluwer. In the morning I spoke about making effective visualizations in Excel, dispensing with the usual tedious “Do this – Don’t do that” approach in favor of a discussion of human visual and cognitive systems. Knowing a bit about these systems makes it easier to understand the rules for effective visual presentations. In the afternoon I led a couple plenary sessions that dealt more specifically with what to do in Excel, following on my earlier discussion of human cognition, and getting into the rules. The day after Excel Experience Day was Excel Experts Day, where I split a day of hands-on training with my colleague and fellow Microsoft Excel MVP Jan Karel Pieterse, who has graced the Excel world with such useful, in fact, indispensable utilities as the Name Manager and AutoSafe. I had nothing but positive experiences in Holland and in all of my interactions with the Dutch people, despite having only a working knowledge of English and no ability to speak Dutch.
Over the past few months I have not spent my customary hour a day on my blog. It dawned on me recently that this hour has been spent practicing guitar, which I’m still not very good at, but which is still a lot of fun. I’m going to have to find another hour somewhere so I can do both. I’d sacrifice an hour of sleep, but already I’m not getting enough of that.
Good to see you back! Thank you for your blog.
Thanks Jon! I’m sorry I only know now that you were in The Netherlands last week. Otherwise I could have attended to this experience day.
Excellent! Man, I’ve been looking for a solution to this for a while. Thanks, Jon, for passing this info along!
Tom
I just noticed something. This is very minor, but if you look closely, the very bottom of your positive values is red, and the very top of your negative values is green. Like I said, very minor, probably imperceivable unless the chart were very large. Just wanted to point it out.
Tom -
Good point. I noticed it too, but using 1% and 99% as the gradient stops keeps the discolored edge to a minimum. If you use 10% and 90%, the effect is exaggerated.
In the limit of 49% and 51%, the bars are essentially two-toned without the gradient. The gradient is still there but it is limited to the center 2% of the bar.
It was nice meeting you again Jon. I enjoyed both days a lot!
I chuckled when I first read this. I had just went to your site two days earlier to lookup up this very function. I tried using the instructions shown for 2003 on my 2010 version and discovered it did not work the same way. So I used my fall back workaround, saved the file as .xls then opened it in Excel 2003. I can then set the formatting save it and reopen in 2010 and re-save it back to .xlsx. Thanks for the tip, well done as usual.
There is no this problem in excel 2010, you can set the color of the negative bars directly.
Liu -
Thanks for pointing that out. I would not have expected that enhancement. But I fired up 2010, and look what they’ve added:
Regarding comment by Jon @ Friday, December 17, 2010, 12:29 pm above
If you change the gradient angle to zero degrees this effect at the edges disappears.
Great solution, Thanks!
Sean
Hello Jon, I just discovered that you’ve cited my red/green trick in Excel 2007. That’s nice, thank you!
I would just like to add that in order to minimize the discolored edge, one must set the gradient stops to 1% and 99% AND the angle to 45 deg (default setting = 90). The picture is shown in my original post: http://www.hichert.com/de/community/foren?func=view&catid=6&id=150
But this is history now, for you can directly set two colors in Excel 2010 :) Also new in Excel 2010 is that you can arrive at the same red/green solution by applying conditional formatting (Solid Fill Data Bar) – very useful!
For more Excel solutions, you can check http://www.istudio.si. Currently only in Slovenian, but since there are many pictures, you might get some ideas on Excel dashboards… English site is coming out in April.
Best regards!
Andrej
Thank you so much!!! I was about to give up on this one and do it manually (and take forever to do something so freakin’ simple!) thanks again!!!
Thanks a lot man!
Is there any way to put this one in a macro? Ive tried to record this, but excel just gave me the chart selection…
hope to hear from you =)
Thanks a lot for sharing the magical gradient. Solved my problem
Very good. I had a VBA solution. But this one is perfect.
Even I cannot understand how excel manage this strange 1% and 99% values.
It works! Thats great! But what do I do with the legend entries!They are always colored in green!
Thanks alot!
If you need different legend entries, use two series, one for positive values and one for negative, and use an overlap of 100%.
But this technique works best when it’s only one series you’re looking at, and you don’t need a legend for that, just an informative title.
Merci pour ce post qui m’enlève une belle épine du pied.
Thanks for this post you got me out of a sticky situation.
Eric
Awesome post!!!Thanks a ton!!
Thank you.
Great Post. This was excellent!!!
Thanks a lot for your post, it’s great.
I like your post as I have a graph in which some of vertical labels need to be in red..
BUT I can not find or get to Format Data Series to check “Invert if negative”
I get the top possiblities, but nothing below Automatic..
Can you help me?
Burdette -
Can you select the series? If not, select another series and click the up/down arrows until the series you want is selected.
With a series selected, press Ctrl+1 (numeral one) and the Format Series dialog will open.