I was just trying to gauge my BI knowledge level the other day and decided to chart it for visibility.

It should be able to tell me to focus on certain topics if 1) I know 20% or less of the topic and 2) if it reaches 100% – which means I need to expand the depth of the topic.

Here is what I want to achieve:

How do I get the conditional colour formatting of the bars in the chart?

Here is how: I created extra columns: the upper and lower target columns, one column for values less than or equals to the lower limit, one for values greater than or equals to the upper limit and one for anything in between. Then chart the data using a stacked chart.

Here is how the data looks like:

And here are the formulas of the first line of the new columns:

• < lower target: =IF($B2<=F2,$B2,NA())
• Acceptable: =IF(AND($B2>F2, $B2<G2),$B2,NA())
• >= upper target: =IF($B2>=G2,$B2,NA())

Drag the formulas down to populate the columns in the other rows.

Chart the data into a stacked bar chart and change the series chart type for the upper and lower targets into line charts.

Stacked Bar Chart:

Change lower and upper targets into line charts

And here is your final chart.

Happy charting!