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.

Example

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?

Solution

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

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

    Reply

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

      Reply

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

    Reply

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

      Reply

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

    Reply

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

    Reply

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

    Reply

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

    Thank you.

    Reply

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

    Reply

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

    Reply

  9. humans

    Reply



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