Excel bar graph change color automatically

Question

There is a table in Excel where I made a bar graph. Each value has a limit value. I want the value to be compared to the limit and then set the column color when loading the table with the values.

I preferred to do it without macro.

Answer 1

I've done the following steps:

1. Add 2 new columns

2. In the first new column wrote the following formula

   =IF(B2< A2;B2;0)

3. In the second new column wrote the following formula

   =IF(B2>=A2;B2;0)

4. Copy and paste the formula in the last 2 columns down the table to fill each row.

5. Click on the insert tab > column chart > stacked column.
6. Set up the three series using columns “=target” and “Target” and give each series a name e.g. “Below target”, “Above target” and “Target”.
7. Add the Horizontal axis labels (the date column) and press the OK button.
8. Right click on the “Target” series and choose “Change series chart type”.
9. Change the series chart type to a normal line graph and press the OK button.

Answer 2

Calcualte the difference between the Limit and the Data in Column D and the Data Base in Column C (see formula below):

       A         B            C                     D
1     Limit     Data   |    Data Base         Difference Limit vs. Data
2       10       11    |     10 =B2-D2          1 =IF(B2-A2>=1,B2-A2,0)
3       10        8    |      8 =B3-D3          0 =IF(B3-A3>=1,B3-A3,0)
4       10       12    |     10 =B4-D4          2 =IF(B4-A4>=1,B4-A4,0)
5       10        9    |      9 =B5-D5          0 =IF(B5-A5>=1,B5-A5,0)
6       10             |      0 =B6-D6          0 =IF(B6-A6>=1,B6-A6,0)
7       10             |      0 =B7-D7          0 =IF(B7-A7>=1,B7-A7,0)

Afterwards create a stacked column chart by using the Datas in Column C:D. The result is the chart below. There you can see the part which is over the limit.