Excel has a built-in feature that allows you to color negative bars differently than positive values. You can even pick colors.
(Chart data is made-up)
All columns in this chart are blue.
- Right click on a column
- Click on "Format Data Series..."
- Click "Fill" button
- Click on "Solid fill"
- Click "Invert if negative"
- Pick a color for positive values
- 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.
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.
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.
Now it is time to build the column chart, shown above.
- Select values in in column A
- Go to tab "Insert" on the ribbon
- Click on "Insert column chart" button
Add data series
- Right click on columns and click "Select Data..."
- Click "Edit" button below "Legend Entries (Series)"
- Click "Series values" button and select cell range B2:B33
- Click OK
- Click "Add" button
- Select cell range C2:C33
- Click OK
The chart changes to this:
You can see that there are gaps between series.
This is what the chart looks like: