How-to Make a Conditional Column Chart in Excel

ConditionalColumnWithAverageLineIntro

As you build your Microsoft Excel dashboards, you may want to highlight your data points that exceed your Key Performance Indicators (KPIs) as well as those data metrics that do not meet your KPI’s.  Many businesses calculate and use Averages as their main KPI breakpoint in their executive dashboards.  In Excel, you can add your own average line to highlight when data points meets that level or do not.  For that matter, if your business has a KPI that is not the average, you can use any Key Performance Indicator line to highlight your dashboard data.

Even more powerful than the average line would be to highlight when the data is above or below the line by conditionally changing the colors of the columns in the Excel chart.  So that if the data point is above the average then have excel automatically color that column green and if the data point is below the average, color that column red.  Let’s show you how you can set up your charts to do this for your Executive Dashboard Template.

Excel’s NA() Function to the Rescue!

image

In a previous post, “How-to Hide a Zero Pie Chart Slice or Stacked Column Chart Section, we showed you how to use the Excel Function NA() to hide a slice of pie or a part of a stacked column chart.  Now we will use the NA() Function to hide entire columns in a chart in this tutorial.

If you chart this range of data that uses the NA() function in a Column Chart:image

It will look like this with the #N/A values not being plotted in the chart at all.  That way we can make a different chart series for every color or condition that we want to display.

image

The Breakdown

1) Make a new chart area for the Conditional Columns to break the data up into 3 series.  One for the values below the average.  One for the values that are equal to or above average and one for the average line.   Use the NA() Function in your formulas for this setup.

image

2) Chart the 3 series.  Noticing that when you chart the 3 series, you will not see any data points that are equal to #N/A.  This is how we hide certain values so that we can color the chart the way we want.

image

3) Change the average series to a line chart type:

image

3) Change the color of the series to suit your purposes.  Change the below average series to Red, the above average to Green.

4) Format the rest of the chart and series to clean up the presentation.  Now you are done!

image

Step-by-Step

1) Here is how your data may look:

image

So we need to create a separate data area for the chart that will create the look that we want.  First, in E2, put in =A2 so that your X-Axis Labels will show be copied down.  Now in H2, put in =$C$2.  Then in F2, put in the formula:   =IF($B2>=$H$2,$B2,NA())   What this formula does is that it compares the value in B2 to the average per month.  If B2 is greater or equal to the average, then the formula will put in the number from B2 and if it is less than the average it will put in #N/A in the worksheet cell.  When we chart these series, the #N/A will not show columns for these values.

image

2) Highlight and Copy E2:H2 then paste from E2 to H5

image

3) Now highlight the new chart data range – E1:H5 and then Click on Insert Ribbon and then Select Column Chart (2D).

image

4) Right Click on the Average Series and Select Change Series Chart Type to Line,

image

or Left click on the Average Series and then choose the Change Chart Type from the Design Ribbon.

ChangeChartTypeMenu

5) Right Click on the Above Average Series and Select Format Data Series…image

or Left click on the Average Series and then choose the Change Chart Type from the Design Ribbon.

FormatSelectionMenu

6) Change the Above Average Series to Plot on Secondary Axis.  Now your data may look strange because the second vertical axis minimum and maximum may not be the same as the primary vertical axis.  Don’t worry, we will fix this in a future step.

FormatDataSeriesSecondAxisDialogBox

7) Select the Fill Sub Menu and Change the Fill to Solid Fill and then choose the color Green

image

8) Now change the Below Average Series color to Red by selecting the series from the chart without closing down the Format Data Series dialog box.

image

9) Now change the Average Series Line color to Blue by selecting the series from the chart without closing down the Format Data Series dialog box.

image

10) Now you see that my data in the picture below looks off, but that is because the Primary and Secondary minimum and maximum values are not the same for the axis.  Once you delete the Secondary Axis, the data and columns will line up.

image

Select the Secondary Vertical Axis and Delete it with your delete key or by right clicking on it and choosing Delete from the menu.

Now we are done and here is what the final chart looks like:

ConditionalColumnWithAverageLine

 

Video Tutorial

 

Free Sample Download Tutorial File

You can download a free sample tutorial file that has detailed instructions and the base formulas so that you can try this on your own.

FREE SAMPLE TUTORIAL FILE LINK:

Conditional-Columns-Sample-File.xlsx

 

Thanks for visiting and please let me know if you found this helpful by posting a comment and signing up for my RSS Feed.  That way you are sure to get the most current Excel Dashboard Tutorial.

Steve=True