## Conditional Formatting of Excel Charts

It’s relatively easy to apply conditional formatting in an Excel worksheet. It’s a built-in feature on the Home tab of the Excel ribbon, and there many resources on the web to get help (see for example what Debra Dalgleish and Chip Pearson have to say). Conditional formatting of charts is a different story.

People often ask how to conditionally format a chart, that is, how to change the formatting of a chart’s plotted points (markers, bar fill color, etc.) based on the values of the points. This can be done using VBA to change the individual chart elements (for example, VBA Conditional Formatting of Charts by Value), but the code must be run whenever the data changes to maintain the formatting. The following technique works very well without resorting to macros, with the added advantage that you don’t have to muck about in VBA.

## Unformatted Charts

Here is the simple data for our conditional chart formatting example.

The data makes a simple unformatted bar chart. . .

. . . or a simple unformatted line chart.

We want our charts to show different colored points depending on the points’ values.

Except for some simple built-in formats, conditional formatting of worksheet ranges requires formulas to determine which cells should take on the formatting. In the same way, we will use formulas to define the formatting of series in the charts.

We will replace the original plotted data in the line and bar charts with several series, one for each set of conditions of interest. Our data ranges from 0 to 10, and we will create series for each of the ranges 0-2, 2-4, 4-6, 6-8, and 8-10.

## Conditional Formatted Bar Chart

The data for the conditionally formatted bar chart is shown below. The formatting limits are inserted into rows 1 and 2. The header formula in cell C3, which is copied into D3:G3, is

``=C1&"<Y<="&C2``

The formula is cell C4 is

``=IF(AND(C\$1<\$B4,\$B4<=C\$2),\$B4,"")``

The formula shows the value in column B if it falls between the limits in rows 1 and 2; otherwise it shows an apparent blank. The formula is filled into the range C4:G13.

When the bar chart is selected, the chart’s source data is highlighted as shown.

We need to change the source data, removing column B and adding columns C:G. This is easily done by dragging and resizing the colored highlights.

The chart now shows five sets of colored bars, one for each data range of interest. It’s not quite right, though, since it’s a clustered bar chart, and each visible bar is clustered with four blank values.

This is easily corrected by formatting any one of the bars, and changing the Overlap property to 100%. This makes the visible bars overlap with the blank bars.

## Conditionally Formatted Line Chart

The data for the conditionally formatted bar chart is shown below. The formatting limits are inserted into rows 1 and 2. The header formula in cell C3, which is copied into D3:G3, is

``=C1&"<Y<="&C2``

The formula is cell C4 is

``=IF(AND(C\$1<\$B4,\$B4<=C\$2),\$B4,NA())``

The formula shows the value in column B if it falls between the limits in rows 1 and 2; otherwise it shows an error, #N/A, which will not be plotted in a line chart. The formula is filled into the range C4:G13.

When the line chart is selected, the chart’s source data is highlighted as shown.

We need to expand the source data, keeping column B as a line connecting all points and adding columns C:G for the separately formatted series. This is easily done by resizing the colored highlights.

The chart now shows five sets of colored markers and line segments, one for each data range of interest.

A little formatting cleans it up. Remove the markers from the original series, remove the lines from the other series, and apply distinct marker formats to the added series.

Remove the unneeded legend entry (for the gray line) by clicking once to select the legend, clicking again to select the label, and clicking Delete.

## Conditional Formatting Flexibility

This simple example has formatting formulas defined based on the Y values in the chart. It is possible to define formatting based on Y values, X values, or values in another column which is not even plotted. As in worksheet conditional formatting, the only limit is your own ability to construct formulas.

This technique works on most useful Excel chart types, including bar and line charts shown here, and XY charts as shown in Conditional XY Charts Without VBA.

## Conditional XY Charts Without VBA

It’s been a busy week, and I haven’t had time to write any new posts. However, in a comment to VBA Conditional Formatting of Charts by Value, Dean asked how to conditionally format points in an XY chart according to which quadrant they fall in, where the quadrants are determined by the mean X and Y values. Raqther than writing a long comment in response, I decided to write a short post.

Of course, you can format any chart’s data points with VBA, according to any conditions you want. But some things are easier to do and less complicated to maintain if you use Excel’s built in worksheet functions. I have covered worksheet-based conditional formatting of charts in Simple Conditional Charts and Simple Conditional Charts – 2. But it’s good to write up more examples of suitable formulas to match the formatting criteria, to help people see when they can use this technique.

To illustrate my answer to Dean’s question, I generated the following data. Columns A and B have the original X and Y data, which I sampled from normal distributions. The mean values for X and Y are computed in A1 and B1. Columns C through F contain the Y values for each of the quadrants, determined by a simple set of formulas.

The formulas in C3 through F3 are as follows, and are filled down to row 14:

C3: =IF(AND(\$A3>=\$A\$1,\$B3>=\$B\$1),\$B3,NA())
D3: =IF(AND(\$A3<\$A\$1,\$B3>=\$B\$1),\$B3,NA())
E3: =IF(AND(\$A3<\$A\$1,\$B3<\$B\$1),\$B3,NA())
F3: =IF(AND(\$A3>=\$A\$1,\$B3<\$B\$1),\$B3,NA())

Here is a chart of the original data, using A2:B14 as the chart’s source data range:

Here is the chart showing the data by quadrant, with one series per quadrant, using column A for all X values and columns C through F for the Y values of the four series:

## Split Data Range into Multiple Chart Series without VBA

In VBA to Split Data Range into Multiple Chart Series I shared a VBA procedure that split a range into separate series in a chart. In fact, this is fairly easy to do using conditional chart formatting techniques I describe on my web site (see Simple Conditional Charts). The way these techniques work is to use formulas in separate columns to capture values from the main data column when certain conditions are met. [Read more…]