How To Show Negatives In A Chart In A Different Color Using The Invert If Negative Feature In Excel

In this tutorial we are going to look at the “Invert if Negative” feature in Excel charts to display negative values in a different color than positive ones.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have 12 months with values for each month, some are positive and some negative. Notice that I have conditionally formatted the values in column B so that the positive ones are green and the negative ones are red. Also note that those colors correspond to the bars on the column chart for those values. I’mm using a RANDBETWEEN formula which is a volatile formula so that each time I press the F9 key, the values refresh:

42516-1

Here you can see the Conditional Formatting formulas I set up for the values in column B:

42516-2

The key is – how to set up the chart so that the positive values show as green (or whatever color you choose) and the negatives as red (or some other color). First, I’ll just create a simple column chart from the data:

42516-3

Next, I’ll right-click on any of the bars, choose “Format data series”, then click on the “Fill & Line” option and put a check mark next to “Invert if negative”:

42516-4

Notice how the bars for the negative values turned clear or transparent. Now I’ll choose the color for the positive numbers, in this case green. As soon as I do, a second color selection option appears, and with that one I’ll choose red. And now my colors are set and with auto adjust as I refresh my data:

42516-5

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , ,

2 Responses to How To Show Negatives In A Chart In A Different Color Using The Invert If Negative Feature In Excel

  1. Marcus August 19, 2016 at 7:13 pm #

    Is there a way to invert marker fills on a line graph?

    • Michael Rempel August 19, 2016 at 11:01 pm #

      Marcus, I don’t know any way to do this. I did some research and the only thing I found was using VBA to accomplish this, but that is an area of Excel that I am not well versed in. Sorry.

Leave a Reply