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:
Here you can see the Conditional Formatting formulas I set up for the values in column B:
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:
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”:
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:
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!