Conditional Formatting in column (bar) charts

flattr this!

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:



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