Author: Oscar Cronquist Article last updated on April 21, 2017

Excel has a built-in feature that allows you to color negative bars differently than positive values. You can even pick colors.

color chart bars by value

(Chart data is made-up)


column chart containing negative values

All columns in this chart are blue.

  1. Right click on a column
  2. Click on "Format Data Series..."
  3. Click "Fill" button
    format data series - invert if negative
  4. Click on "Solid fill"
  5. Click "Invert if negative"
  6. Pick a color for positive values
  7. Pick a color for negative values

Color chart columns by a condition

What if you want to color bars by comparing?

This chart example shows bars colored differently depending on preceding value. If a value is larger than the previous one, it will be colored blue. Smaller than the previous value and the bar will be red.

color bars - chart1

How to build

The trick here is to split data into two different chart series, you can do that by placing them in two columns using formulas.

color bars - data

Formula in cell B2: =IF(A3>A2,A3,"")

Formula in cell C2: =IF(A3<A2,A3,"")

Copy these cells and paste them on cells below, as far as needed.

color column chart by comparing values4

Now it is time to build the column chart, shown above.

  1. Select values in in column A
  2. Go to tab "Insert" on the ribbon
  3. Click on "Insert column chart" button

Add data series

  1. Right click on columns and click "Select Data..."
    color bars by value - select data source
  2. Click "Edit" button below "Legend Entries (Series)"
    color bars by value - edit series
  3. Click "Series values" button and select cell range B2:B33
  4. Click OK
  5. Click "Add" button
  6. Select cell range C2:C33
  7. Click OK

The chart changes to this:

color bars - chart

You can see that there are gaps between series.

  1. Right click on a column
  2. Click "Format Data Series..."
    color bars - format data series
  3. Change "Series Overlap" to 100%
    color bars - format data series1

This is what the chart looks like:

color bars - chart1

Download excel 2013 *.xlsx file

Color bars by value.xlsx