Conditional Formatting – Chart Data Labels

This week in the Chandoo.org Forums, Greg asked the question, “I would like to conditionally format the data labels position to be above the plot line in a scatter plot if a certain cell contains ‘True’ and below the plot line if that cell contains ‘False’.”

Greg also wanted a Non-VBA Solution.

This post will describe how this is achieved as well as extend the idea into the fourth dimension.

All the charts in this post are available in the sample file: Download Sample File.

 

The Concept

The concept applied here to achieve the final result that Greg wants is that charts can use multiple data series.

These data series do not have to be visible but they can, at the same time, have Data Labels or other formatting applied.

The Application

First setup a set of data,

I have used values A to P as X Axis Labels and used a formula =Randbetween(10,20) in column C for the Y Values for the chart

Now add a Data Validation to a cell G3

Goto the Data, Data Validation Tab and select Data Validation

next add 2 columns

D3: =IF($G$3,C3,NA())

E3: =IF($G$3,NA(),C3)

Copy these down to Row 18

Select the Range B3:E18, note it includes the X Axis Labels and Headers

Now goto the Insert, Chart tab and select the chart type you want to use. I have chosen a Line Chart

Excel will draw a Chart with 3 series of lines

Now is a simple job of applying labels and formatting as applicable

The first thing to notice is that the chart has 3 series, Random Value, True and False

We can only see the True series, as it is in front of the Random Value series, The False series is hidden for now.

Select the True Series by Clicking on it

Then Right Click on it and Add Data Label

Excel adds the Data Labels to the True Series

Right click on any of the Data Labels and select Format Data Label

 

For the True values we will plot them above the Data Point

Change the values as shown above

Right click on the Data Series Line (the orange line) and select Format Data Series

Change the Line Type to No Line

The Orange line is gone and there is now a Blue Line, this is the Random Values series

Note we can still see the Data Labels for the True Series, even though the True Series Line is not visible

You can set or disable markers whilst you are here as well

Next select the False Series, by changing the Data Validation cell to FALSE

We can now see the False Data Series and the Random Values Series which is behind the Grey Line as before.

Right click the False Data Series, Add Data Labels

Then Right Click the New Data Labels and Change there settings to be below

Finally set the False Data Series Line Line Type to No Line

Now we can see the Rand Value series (Blue line) with the Data Labels showing for the False Series below the line

Change the Data validation from True to False and vice-versa and observe that Excel is only showing the series Labels for the Data Series which has values and doesn’t have #N/A errors in Columns D & E

So we are seeing 3 Series and 2 sets of Data Labels, it is just that we have set Two of the Line Types to No Line and Excel doesn’t display Series Values where the Value is the error value #N/A.

Now set the data Validation to True and select the Data Labels Font Color to Blue

Repeat the Process for the False Data Labels and set them to Red

Finally clean up the legend

Select the Chart, then click on the legend

Then click on TRUE and press the Delete Key

Repeat for the FALSE Legend

Our Final Chart

Change the Data Validation cell to True/False to verify that the system is working.

The techniques described above can be applied to most chart types.

Care must be taken with Column and Bar and other cumulative chart types.

 

Extensions

Having seen how Excel treats the #N/A error we can use that to create a number of variations for our Data Labels

 

Conditionally Format Data Labels above and below a set value

This is achieved by using a formula that applies to individual data points in each series

so that when a Data Point in a series (>15) is less than 15 it will return a #N/A error and not be displayed and also when a Data Point in a series (<=15) is greater than 15 it will return a #N/A error and not be displayed

 

Add a Third or more Set of Conditional Data Labels

This is achieved by simply adding a Fourth Data Series to the chart and adjusting the formulas as appropriate

 

Add Conditional Formatted Text Data Labels to Highlight Points

These are achieved by using the above techniques but instead of Displaying Values for the Data Label Series, we use the Value From Cells option

 

Add Conditionally Formatted Markers to Highlight Points

This is achieved by using the above techniques but alter the markers for the two helper Columns as well as the Data Labels

 

Explore

You can explore how these are constructed using the sample file.

All the above charts are shown in the sample file: Download Sample File.

 

Selecting Chart Series

One of the annoying aspects of dealing with charts and formatting individual series is the ability to select hidden or covered series

Fortunately there are a number of ways to get around this.

 

Use the arrows Keys

In older versions of Excel, you can select a Chart, then use the Up/Down arrow keys to cycle through all the chart objects.

Once you had the object you wanted Press Ctrl+F1 to bring up it’s format Properties

Unfortunately Microsoft in its wisdom has removed this functionality in recent versions of Excel, so try it, If it works, Enjoy, If it doesn’t keep reading

 

Use the Tab Menu

If you select a Chart you will see two extra menu items on the Tab Menu

These are the Chart Design and Chart format Tabs

Select the Chart Format Tab

Then Goto the Drop down on the Far Left of the Tab

It contains a list of all the available Chart Objects,

Select the Chart Object you want, then press Ctrl+1 to bring up the format options

 

Use the Chart Format Menu

If you select a Chart and select any part of the chart press Ctrl+1 and the Format Menu for that object is shown

Now use the small drop down just under the Format Title and select the Object you wish to change

 

Warning

Despite being able to use the Excel =NA() function to force an #N/A error, which is ignored by Excel, future versions of Excel maybe about to change this behavior.

Some people using the Excel 365 Insider Fast Edition are noticing a new Dialog option.

So keep in mind if all of a sudden this behavior changes, you may have upgraded Excel and introduced this new menu

You can read more about how to use this new functionality here:

http://www.exceluser.com/excel_dashboards/two-business-uses-for-excels-new-chart-feature.html

Comments

If you have any other ideas about how to use this functionality let us all know in the comments below