Author: Oscar Cronquist Article last updated on October 14, 2019

This article demonstrates how to set up a chart so it shows one color for increasing bars/columns and another color for decreasing bars/columns.

The image above shows a chart with blue and red columns, the blue columns are increasing based on the previous column and the red columns are decreasing meaning they are smaller than the previous column.

Peter asks:
Hello,
I’m new working with dynamic charts using Excel 2007. I created a dynamic bar chart using 2 series of yearly sales. I have defined range names FW for series 1 and SS for Series 2. I would like to display each respective bar from each series in blue each time sales increase from the previous year and in red each time the sales decrease from previous year.
How would I go about doing this?
Thanks.
Peter

The image below shows the data behind the chart above, two formulas are used to separate decreasing and increasing values from the original source in cell range B3:C12.

It is important that increasing values are in one column and decreasing values are in another column. This makes it easier to separate the values into different chart series.

Increasing values are extracted with the following formula in cell E3:

=IF(ISTEXT(C2),C3,IF(C3>C2,C3,""))

Decreasing values are extracted with the following formula in cell F3:

=IF(ISTEXT(C2),"",IF(C3<C2,C3,""))

Copy cell E3 and paste to cells below as far as needed, repeat with cell F3.

Explaining formula in cell E4

Step 1 - Check if above cell is text

The ISTEXT function will tell us if the cell is the first one in the column. We want to avoid comparing a number with text.

IF(ISTEXT(C3),C4,IF(C4>C3,C4,""))

becomes

IF(FALSE,C4,IF(C4>C3,C4,""))

Step 2 - Compare number with cell above

The IF function allows you to return a value if the logical expression evaluates to TRUE and another if FALSE.

IF(FALSE,C4,IF(C4>C3,C4,""))

becomes

IF(FALSE,C4,IF(39>25,C4,""))

becomes

IF(FALSE,C4,IF(TRUE,C4,""))

and returns 39 in cell E4.

Setting up the chart

We now have data in separate columns, this way we can use two data series to color bars/columns differently.

  1. Select cell range E3:F12.
  2. Go to tab "Insert" on the ribbon.
  3. Click "Insert column or bar chart" button.
  4. Click on "Clustered column" button.
  5. Right click on chart.
  6. Click "Select Data...".
  7. Click "Edit" button.
  8. Select cell range E3:E12.
  9. Click OK button.
  10. Click the other "Edit" button.
  11. Select cell range B3:B12.
  12. Click OK button.
  13. Click the "Add" button.
  14. Select cell range F3:F12.
  15. Click OK button.
  16. Click OK button.

Download Excel file


Copy-of-Colored-bar-chart.xlsx