Create Dynamic Excel Chart Conditional Labels and Callouts

Create Dynamic Excel Chart Conditional Labels and Callouts
Create Dynamic Excel Chart Conditional Labels and Callouts

Chart Conditional Labels and Callouts

Learn how to make the simple and easy these Excel Chart Label Callouts:

Custom Labels based on a Formula or Threshold
Custom Labels based on a Formula or Threshold
Custom Label Callouts for Individual Data Points
Custom Label Callouts for Individual Data Points

In my most recent article, I described how Excel is taking user feedback for changes to the MOST AWESOME PRODUCT IN THE WORLD 🙂  You can check out the post here:

Do you have an Idea to Make Excel Better? Excel’s Suggestion Box is Here!

One of the Ideas submitted by users is as follows:

“Adding comment (tool tip) in chart (to various elements of the chart, data points, etc.)

To be able to add comments to a specific data labels in a chart (can be bar chart, line chart) -Kreshna Fowdar”

So it got me thinking how can we use standard Microsoft Excel Charting features to Quickly and Easily mimic this request?  And in my testing, I developed a whole NEW way of thinking about Chart Conditional Labels and Callouts for Column and Bar Charts.

Below you will find my instructions, video tutorial, and free sample Excel file to create the following charts:

  1. Custom Labels based on a Formula or Threshold
  2. Custom Label Callouts for Individual Data Points

A) Custom Labels based on a Formula or Threshold

Custom Labels based on a Formula or Threshold
Custom Labels based on a Formula or Threshold

The Breakdown

This chart will allow you to enter in 1 value for a threshold and apply that to both the Labels as well as the Conditional colors of the Column chart.

  1. Add 2 New Chart Data and Label Columns
  2. Create Excel Column Chart or Excel Bar Chart
  3. Add Data Labels to New Chart Data Column
  4. Change Labels to Category Name
  5. Move New Chart Data Column to 2nd Axis
  6. Change Horizontal (Category) Axis Label Range
  7. Delete 2nd Vertical Axis

Step-by-Step

1) Add 2 New Chart Data and Label Columns

For our sample chart, we will start with this sample data in Cells A3:B15:

AB
1
2
3Scores
4John47
5Frank78
6Mary33
7Steve65
8Joanie96
9Svetlana23
10Jamal95
11Diana79
12Cate99
13Zach21
14Evan61
15Henry72

For this chart, we need to add a placeholder for the Threshold (Cell D1) and for the Custom Label (Cell D2) that we wanted to apply in the chart.  Also, you will need to add the following formulas in Cell C4 =IF(B4>$D$1,B4,0) and D4 =IF(VALUE(C4)>0,$D$2,””) respectively and copy the formulas down to the end of the range C4:D15.

The formula in Column C will use the value you put in cell D1 to determine if it meets the threshold.  If it does meet the threshold then the Label put into cell D2 will then populate the range in Column D.  The chart wil then use Column C as the position on the chart and Column D will be the Labels shown in the chart.

ABCD
1Threshold79
2LabelPassing
3ScoresPassingLabel
4John47
5Frank78
6Mary33
7Steve65
8Joanie96
9Svetlana23
10Jamal95
11Diana79
12Cate99
13Zach21
14Evan61
15Henry72
Worksheet Formulas

CellFormula
C4=IF(B4>$D$1,B4,0)
D4=IF(VALUE(C4)>0,$D$2,””)

2) Create Excel Column Chart or Excel Bar Chart

The data is now all setup to make the chart.  So let’s create it now.

First, highlight cell range A3:C15 and then click on the Insert Ribbon.  Next, click on either a Clustered Column Chart or a Clustered Bar Chart.

Insert Column Chart
Insert Column Chart

Your chart should now look like this:

Insert Clustered Column Chart for Dynamic Label Threshold Chart
Insert Clustered Column Chart for Dynamic Label Threshold Chart

3) Add Data Labels to New Chart Data Column

As we want to add a custom threshold label on the chart, we need to add labels.  Don’t worry if they are not the correct ones yet, we will change that in a few steps.

To add labels, click on the chart, then click on the Red Column for the passing data series.  Then click on the Layout Ribbon and press on the Data Labels button as you see here:

and then choose “Outside End”

Your chart should now look like this:

Add Data Labels Outside End for Dynamic Label Threshold Chart
Add Data Labels Outside End for Dynamic Label Threshold Chart

4) Change Labels to Category Name

This solution does not use Value labels.  Instead, we need the Category Name.  To change your Data Labels of your Excel Chart to Category Name, first, click in the chart, then double click on the data labels for the series.  Then from the Format Data Labels dialog box change the “Label Contains” option from Value to Category Name as you see here:

Change Series Data Label to Category from Value
Change Series Data Label to Category from Value

Click on the Close button and your chart should now look like this:

Change Data Labels to Category for Dynamic Label Threshold Chart
Change Data Labels to Category for Dynamic Label Threshold” Chart

5) Move New Chart Data Column to 2nd Axis

Now you may be wondering why we did that last step of adding Category Name Labels as it looks just like the horizontal axis.  Well, in order to show different Category Names we need to move this series to the secondary axis.  To do that, first, select the chart, then double click on the Red Column for the Passing data series.  This will open the Format Data Series dialog box.  Then change the “Plot Series on” option to Secondary Axis.

Move Series to Secondary Axis Dialog Box
Move Series to Secondary Axis Dialog Box

 

After changing this option, the chart will look like this:

Move Series to 2nd Axis for Dynamic Label Threshold Chart
Move Series to 2nd Axis for Dynamic Label Threshold Chart

Note: It is a personal preference as to the color of the 2nd axis chart series.  Default, Excel will change the color like you see here to Red.  If you want the color to match the primary axis series, you will want to change the Fill color to “No Fill” in this step.

6) Change Horizontal (Category) Axis Label Range

Here is the real Tip and Trick that makes this Microsoft Excel Chart technique so work well.  Now that we have our label data series on the secondary axis, we can assign a different range for it’s category labels.  That way the new category range will show up as our labels.

However, the secondary category axis labels are not apparent that they are in fact different than the primary axis.  Microsoft Excel defaults the Horizontal Category Axis Labels as the same range as the primary axis unless you change them manually.

To change the 2nd Axis Horizontal (Category) Axis Labels to another range, follow these steps.  First, click anywhere in the chart.  Then select the design ribbon and then choose the “Select Data” button in the Data group.

Select Data Button on the Design Ribbon
Select Data Button on the Design Ribbon

Next, from the Select Data Source Dialog box, click on the “Passing” series in the Legend Entries (Series) section.  Then with that series selected, click on the “Edit” button in the Horizontal (Category) Axis Labels Section as you see here:

Select Data Source Dialog Box of Passing Series for Dynamic Label Threshold Chart
Select Data Source Dialog Box of Passing Series for Dynamic Label Threshold Chart

Then from the Axis Labels dialog box, drag and drop in the spreadsheet to highlight the range for the Secondary Axis Category Labels.  In this case, you will want to highlight cells D4:D15.

2nd Axis Label Range for Passing Series of Dynamic Label Threshold Chart
2nd Axis Label Range for Passing Series of Dynamic Label Threshold Chart

Click on the OK buttons until you are back to the spreadsheet and your chart is almost complete and will now look like this:

New 2nd Axis Category Names for Dynamic Label Threshold Chart
New 2nd Axis Category Names for Dynamic Label Threshold Chart

7) Delete 2nd Vertical Axis

To finish the Dynamic Excel Chart Conditional Labels, we just need to clean things up a bit.  I HIGHLY recommend deleting the secondary vertical axis.  I highly recommend it because Excel will change that axis based on your threshold limit in cell D1 to fill up the chart the way it sees fit.  You can learn more about why Excel does that here: Problems with an Excel Dashboard Goal Chart

Also, you may or may not want to delete an entry in the Legend or delete the legend entirely.  It is a user choice on if the legend is needed or is confusing.  In our case, I deleted both and your final chart will look like this:

Custom Labels based on a Formula or Threshold
Custom Labels based on a Formula or Threshold

B) Custom Label Callouts for Individual Data Points

Custom Label Callouts for Individual Data Points
Custom Label Callouts for Individual Data Points

The Breakdown

This chart will allow you to enter a custom label for ANY data point along the Clustered Column Chart or Clustered Bar Chart of your choosing.  Simply put in the custom label text next to the value that you want to highlight.

The steps are the same as the tutorial above, except that you will have different formulas in columns C and D.

Step-by-Step

For this chart, use this setup and change the formulas in cell C4 to  =IF(LEN(D4)>0,B4,0) and there is NO formula in Column D.

ABCD
1
2
3ScoresLabel HeightComment
4Jan470
5Feb780
6Mar330
7Apr650
8May960
9Jun230
10Jul950
11Aug790
12Sep990
13Oct210
14Nov610
15Dec720
Worksheet Formulas

CellFormula
C4=IF(LEN(D4)>0,B4,0)

Then follow the rest of the steps presented for the first chart.

Then as you enter any text in column D next to the data you want to callout in the chart, your text will appear in the graph as you enter them as you see here:

Custom Label Callouts for Individual Data Points in an Excel Chart
Custom Label Callouts for Individual Data Points in an Excel Chart

Video Demonstration

Check out this Video tutorial of the techniques presented above.

 

Sample File Download

Click here to Download the Free Sample Excel Template File: Create-Dynamic-Excel-Chart-Conditional-Labels-and-Callouts.xlsx

 

Let me know what you think of this technique in the comments below.  Also, do you have any favor Excel Chart Tips and Tricks?  Let me know those as well!

 

 

Steve=True

LEAVE A REPLY

Please enter your comment!
Please enter your name here