Different Colours for Positive and Negatives in an Excel Column Chart

• 2 mins read

Try to avoid red and green as they can be hard to differentiate for people with colour blindness (mainly men).

In the image below we can see the standard chart with the same colour for positive and negative values.

To use different colours you need to use a slightly different layout and a couple of hacks.

Different layout

You need to separate the positives and negatives values into separate columns – see image below.

NA() function

Excel has an NA() function that returns the #N/A error. This is used to stop Excel plotting something. In this case rather than show zero or a blank the formula in cell E2 returns #N/A for negatives.

The formula in cell F2 is similar – see image below.

Creating the chart

Select the ranges as per the image below. Hold the Ctrl key down to select separate ranges with the mouse.

Click the Insert ribbon tab and use the top left chart in the column chart drop down – image below.

The image below is close to what we need but we need one more hack to make it look a bit better.

Currently the columns are not directly above the month name. We need to adjust the Series Overlap percentage to fix that.    

Click on one of the columns and press Ctrl + 1.

In the task pane that opens on the right change the Series Overlap to 100% – this will line up the columns above the month name.

Tidying Up

To finish delete the legend.

Link the Chart Title to cell A1. To do that click the Chart Title then click in the Formula Bar and type =  and then use the mouse to click cell A1 and press Enter.

Move the X axis (horizontal) to the bottom of the chart. To do that click on the horizontal axis. Click the last icon at the top of the task pane. In the Labels section click the Label position and choose Low.

Final chart is shown below.