AJP Excel Information AJP Excel Information

Invert if negative


For column, bar, cylinder, cone and pyramid charts there is an option to automatically  display an alternative colour if the value is negative. This feature can be very handy but configuring it is awkward to say the least.
How To Use 'Invert if negative'
Lets start with a normal column chart. Negative bars are currently coloured the same as positive.
From the Format Data Series dialog we can enable the 'Invert if negative' option.

This is how the Invert if negative will work according to the help.
(Note: pie charts do not plot negatives and the option is not available for either pie or area)
The result is a different colour for the negative values. 
So now we want to select a suitable negative colour.
If we once again display the Format Data Series dialog and press Fill Effects we will see the dialog shown below. The Foreground colour matches the positive bars and the Background colour matches with the negative bars.
So we change the background colour to the desired colour for our negative bars.
Now you may have noticed that all the Patterns contain a mixture of Foreground and Background. As we want solid colours we will have to select the pattern with the least intrusive pattern, which is the 5% pattern.
So now the bars are have different colours for positive and negative values.
BUT wait, they are the complete opposite of what we want!
Lets go back to the Fill Effects dialog and swap the colour selection.
That's better, we now have the right colours for the positive and negative bars. Although our chart looks as though it has measles.
To get rid of those spots go back to the Format Data Series dialog and select the required colour for the positive bars.
There we go a chart with solid positive and negative colours.

See the tip on how to 'Make X axis labels display below negative data'

This technique does not work in xl2007. The changing of the colour in the final step causes the chart to revert back to default invert colour.


Created August 2004
Last updated 5th August 2014 

Return to main page Chart Section VBA section Fun and games section Forum files Tips section Links section Book section Site information Site Search RSS feed Top of page

Microsoft® and Microsoft® Excel are registered trademarks of the Microsoft Corporation.
andypope.info is not associated with Microsoft. Copyright ©2007-2016 Andy Pope