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 »15 comments to this article

  1. Sometimes the most beautiful answers are the most simple. Thank you!


  2. Hi,

    how can I create colored chart in Excel 2016? I want to have different data curve displayed by different colors. How to set this option to chart settings?


    • Hi,

      I think this example should also work in Excel 2016.


  3. This solution is great for Excel chart where color is depending on value.


    • How to dynamically change color in chart? I want to create chart from values and make colors depending on values.


      • Hi,

        Excel does not support conditional formatting in charts. But there is a solution. Read this article. You have there examples. If you have some specific issue we can help you.


  4. Can you show us an example for conditional formatting in chart with multiple conditions? Can I use the temp table for this?


    • It is simillar as for 2 conditions. You have to sort data into columns a create chart from them.


  5. How to change color in chart using Conditional formatting in Excel file?


  6. Is there somewhere more solutions like that? This is really great idea for chart conditional formatting. I want to create my own Excel hints database.


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


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

    Thank you.


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


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


  11. humans


Leave a Reply to Linn Cancel reply

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