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.

Source table - example1

Simple chart for this table looks like this:

Simple column chart - example1

How to automatically highlight columns?


Step 1

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).

New data table - example1

Formulas for item A:

Cell B10: =IF(B2>=20,B2,"")
Cell C10: =IF(B2<20,B2,"")

Step 2

Create stacked column chart from source A9:C15. Result is working like a column chart with conditional formatting.

Stacked column chart

And the final result:

Conditional formatting chart - example1

Watch this in video tutorial:

Add a comment »5 comments to this article

  1. Can I somewhere download this example in Excel format?


  2. Really simple and easy solution of conditional formatting in Excel graph.

    Thank you.


  3. The new XRumer 16.0 – revolution in online promotion:
    artificial intelligence will help you to attract customers so effectively,
    more than ever!


  4. Hello)
    XRumer16.0 is coming soon
    Good luck!


  5. humans


Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress