fbpx
Search
Close this search box.

Conditional Formatting – Chart Data Labels

Share

Facebook
Twitter
LinkedIn

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

 

 

 

 

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

10 Responses to “Conditional Formatting – Chart Data Labels”

  1. Gino says:

    As always, Hui - this is brilliant. Thank you so much for sharing!

  2. Greenbriars says:

    I loved these charts, however I have a question.

    Where you have used the "On Target" labels I get what appear to be random results which I do not understand. Sometimes I get no labels whatsoever (most frequent). At other times I may get a single label if there are multiple occurrences of the value.

    I initially wondered whether the problem may be that there was insufficient room to display the label. I moved the chart to a new sheet which filled the screen. Same result.

    I have also given the labels a background so that I can see that the labels appear against the data points, but I cannot get the text to appear against more than one data point at most. I have even tried changing the "On Target" text to a single character with no success.

    I am using Excel 2016 32 bit version on Windows 10 Professional 64 bit.

    Does this apply just to me?

  3. Rahul says:

    Excellent trick for conditional formatting the chart.

  4. Ryan says:

    Couldn't you have accomplished the same thing by formatting the data label as something like

    [Blue][>15]0;[Red][<15]0;"On Target"

  5. Karlo says:

    Chandoo I tried opening your file but the label "on target" does not appear. I have excel 2010

  6. Karthik says:

    Hi,

    How do I change the data type of the graph data labels dynamically depending on the value of a particular cell. For instance, If the cell A1="abc" the data type in the graph should be percent, else, it should be number

    Thank you

Leave a Reply