Author: Oscar Cronquist Article last updated on December 05, 2018

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 following image shows the data behind the chart above, two formulas are used to separate declining and increasing values.

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 *.xlsx file

Copy of Colored-bar-chart.xlsx