Conditional Formatting in column (bar) charts
Excel does not support conditional formatting in charts, but we can solve it with a little trick. Original data will be split into a new table and then will be displayed by Stacked Column chart. The result will look like conditional formatting.
Let us have following data table. We want to highlight a column in chart having a value less than 20.
Simple chart for this table looks like this:
How to automatically highlight columns?
Make a new table having values in 2 columns. In the first are values greater than or equal to 20. In the second column are the rest (values less than 20).
Formulas for item A:
Cell B10: =IF(B2>=20,B2,"") Cell C10: =IF(B2<20,B2,"")
Create stacked column chart from source A9:C15. Result is working like a column chart with conditional formatting.
And the final result:
Watch this in video tutorial: