Expert Zone
XLSX + XLS repair tool

Formatting 'Invert if Negative' for Column and Bar Charts


Andy Pope

Both column and bar charts give you the option to invert the area colour of any bar that has a negative value. What is not clear though is how to control the colour used for the negative bars.

Start by double clicking the series, in order to display the Format Data Series dialog, and on the Patterns tab check the Invert if negative option. Click the Fill Effects button and go to the Pattern tab.

Figure 1 illustrates the default settings of the fore and background colours.

Here you will see that the Foreground colour matches the positive bar and the Background colour matches the negative colour. But in order to set the colours as we want you will need to reverse them. So set the Foreground colour to be that of the required negative bar and the Background colour to be that of the positive bar. You will temporarily have to select a pattern, the 5% pattern will do fine, as 0% or solid is not an option.

Figure 2 illustrates the dialog after these choices have been made.

Click OK to close both the Fill Effects and Format Data Series dialogs. You should now have bars that are coloured, albeit with a slight pattern, as you want for both positive and negative values.

To complete the formatting double click the series again, in order to display the Format Data Series dialog. On the Patterns tab select the positive colour as the Area colour.

Figure 3 illustrates the setting of the Area pattern to solid.

The same technique will work for the variations of column and bar charts such as Cylinders, Cone and Pyramids.