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.
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?
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:
Decreasing values are extracted with the following formula in cell F3:
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.
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.
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.