Invert If Negative in Versions of Excel Later than 2007
[Updated 77 July, 2018] If you’re using a newer version of Excel than 2007, you don’t need to follow the convoluted protocol that was required in Excel 2007.
In Excel 2016, 2013, and even as early as 2010, Invert If Negative has been accompanied by dual color selection controls. Select Solid Fill and Invert if Negative, then choose the positive color on the left and the negative color on the right.
If you’re still using Excel 2007, well, it’s long past time to upgrade.
Invert If Negative in Excel 2007
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.