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.

Column Chart with Invert if Negative Setting

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=150

It 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.

Column Chart without Invert if Negative Setting

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.

Excel 2007 Dialog Showing Invert if Negative Setting

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.

Column Chart with Invert if Negative Setting

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.

Excel 2007 Dialog Showing Simple Gradient Step 1

Set Stop 2 at position 100% with the second color, red for negative.

Excel 2007 Dialog Showing Simple Gradient Step 2

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.

Column Chart with Simple Gradient and Invert if Negative Setting

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:

Gradient Settings for Invert if Negative

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.

Column Chart with Funky Gradient and Invert if Negative Setting

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.

Peltier Tech Chart Utility

Comments

  1. Good to see you back! Thank you for your blog.

  2. 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.

  3. Excellent! Man, I’ve been looking for a solution to this for a while. Thanks, Jon, for passing this info along!

    Tom

  4. 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.

  5. 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.

    Invert if Negative Setting Not Optimum

    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.

    Invert if Negative Setting Way Not Optimum

  6. It was nice meeting you again Jon. I enjoyed both days a lot!

  7. 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.

  8. There is no this problem in excel 2010, you can set the color of the negative bars directly.

  9. Liu -

    Thanks for pointing that out. I would not have expected that enhancement. But I fired up 2010, and look what they’ve added:

    Invert if Negative in Excel 2010

  10. 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

  11. 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

  12. 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!!!

  13. 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 =)

  14. Thanks a lot for sharing the magical gradient. Solved my problem

  15. 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.

  16. It works! Thats great! But what do I do with the legend entries!They are always colored in green!

    Thanks alot!

  17. 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.

  18. 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

  19. Awesome post!!!Thanks a ton!!

  20. Thank you.

  21. Great Post. This was excellent!!!

  22. Thanks a lot for your post, it’s great.

  23. 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?

  24. 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.

  25. WOW – this is amazing – I don’t know how long I have been looking for the solution for this problem!

    Thank you!

  26. Great, It works!…. Thanks a lot for your post

  27. That was super-helpful, thanks!
    Just one thing I noticed about the discoloured edge people mention above. One person said to set it to 0%, another said 45%. The actual answer depends on the type of graph it is. Setting the angle to 0 degrees for column graphs, and 90 degrees for bar graphs will eliminate the discoloured edge altogether! :-)

Subscribe without commenting

Trackbacks

  1. [...] This post was mentioned on Twitter by David Napoli, SuSapta. SuSapta said: Invert if Negative in Excel 2007 | Peltier Tech Blog | Excel Charts http://bit.ly/gH0n9O [...]

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites