# Chart Conditional Labels and Callouts

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

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.)

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

### 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:

A B
1
2
3 Scores
4 John 47
5 Frank 78
6 Mary 33
7 Steve 65
8 Joanie 96
9 Svetlana 23
10 Jamal 95
11 Diana 79
12 Cate 99
13 Zach 21
14 Evan 61
15 Henry 72

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.

A B C D
1 Threshold 79
2 Label Passing
3 Scores Passing Label
4 John 47
5 Frank 78
6 Mary 33
7 Steve 65
8 Joanie 96
9 Svetlana 23
10 Jamal 95
11 Diana 79
12 Cate 99
13 Zach 21
14 Evan 61
15 Henry 72
Worksheet Formulas

Cell Formula
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.

Your chart should now look like this:

#### 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:

# If you found the website and tutorials helpful, please consider donating to keep the lights on.

## Donate with PayPal here:

#### 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:

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

#### 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.

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

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.

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:

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.

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

#### 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:

## B) 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.

A B C D
1
2
3 Scores Label Height Comment
4 Jan 47 0
5 Feb 78 0
6 Mar 33 0
7 Apr 65 0
8 May 96 0
9 Jun 23 0
10 Jul 95 0
11 Aug 79 0
12 Sep 99 0
13 Oct 21 0
14 Nov 61 0
15 Dec 72 0
Worksheet Formulas

Cell Formula
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:

## Video Demonstration

Check out this Video tutorial of the techniques presented above.

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