Peltier Technical Services, Inc.
 

Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog


Peltier Tech Charts for Excel 3.0

 

Books at Amazon.com

 

Invert Excel Chart Format if Negative


 

This page shows how to use the Invert if Negative formatting option for a single series, to display positive and negative values using different formats.

The original chart in this example is a simple column chart, with some positive and some negative values (see Deviation Column Charts for a more complete treatment).

To use the Invert if Negative setting, double click the series, and check the Invert if Negative box under the color selection palette in the Patterns tab (indicated by the encircled checkbox at right). This produces the chart below.

    

Excel has inverted the negative columns to use the default second color (white), but the Patterns tab of the Format Series dialog only allows you to specify the first color.

    

To choose a second color, double click the series again (or do this in the same double click where you selected Invert if Negative). On the Patterns tab, click the Fill Effects button under the Area color palette, to see the Fill Effects dialog (left). Select the Two Colors option (indicated by the red arrow), then pick the desired color using the Color 2 dropdown. Click OK twice, to get the chart below.

The gradient fill is generally unnecessary chart junk*, but your chart needs to show them, for a moment at least, so your choice of second color sticks. Double click the chart again, and in the Patterns tab, click again on the first color in the Area color palette. Click OK, and you get solid columns, with positive values shown in your selected color 1, and negative values shown in your selected color 2.

*see General Charting and Graphics for books that show how to make your charts more informative and less cluttered with unnecessary "chart junk".

The resulting chart is shown below.

 

Page copy protected against web site content infringement by Copyscape

Peltier Tech Charts for Excel 3.0


Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2017. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

Microsoft Most Valuable Professional

My MVP Profile