## Plot Values Along the Axis of an Excel Chart

A blog reader emailed me with a question:

I want to visualize a series of prices on single axis:

I want to portray these in correct proportion, horizontally along a single line – much like points on a thermometer.

These prices are going to be the X values. You need some kind of dummy Y values, so Excel knows where to put the points.

In the data range shown below, I’ve put a set of dummy values in the row below the prices, using 0.5 as a dummy value. I picked 0.5 to float the points in a line above the horizontal axis; if you do in fact want the points right on the axis, you could use 0.0 for the dummy Y values.

I also changed the product numbers to alphabetic labels for this illustration. Short names will work best as data labels in the chart.

I made a chart using the prices and the dummy Y values (the shaded cells in the sheet). That’s the big chart below the data.

First I shrunk the chart and the chart title, and changed the title to something more descriptive.

I removed the gridlines, and set the Y axes to a min of 0.0 and a max of 0.7, which made the labels fit better.

The Y axis scale is something that can be easily adjusted as needed. In fact, I didn’t really set it here, I set it later, after I saw how my chart looked with the labels. If you plot the points along (not above) the X axis, you may want to pick a Y axis minimum which is the negative of the maximum.

Next I hid the vertical axis by using no line color and no labels.

I also set the X axis scale to a min of 1.5, to spread out the points a bit.

Next I set the plotted points so the format varied by point; this setting is found where you set the fill color of the markers.

I also added data labels; by default Excel uses the values in the labels, and placed them above the points.

Finally I set up the custom labels I wanted. I used the X values instead of the default Y values and also used the Value from Cells option to get the product names into the labels, with a new line separating the cell values and the X values. These settings are shown in the Format Labels task pane shown below. Unfortunately if you do not have Excel 2013, you don’t have the Values from Cells option, but you have other ways to Apply Custom Data Labels in Excel Charts.

I also changed the font color of each label to match the corresponding point, and staggered labels where they were too close by placing a couple below the points.

## Apply Custom Data Labels to Charted Points

Often you want to add custom data labels to your chart. The chart below uses labels from a column of data next to the plotted values.

When you first add data labels to a chart, Excel decides what to use for labels—usually the Y values for the plotted points, and in what position to place the points—above or right of markers, centered in bars or columns. Of course you can change these settings, but it isn’t obvious how to use custom text for your labels.

This chart is the starting point for our exercise. It plots simple data from columns B and C, and it displays only the default data labels, showing the Y values of each point.

There are a number of ways to apply custom data labels to your chart:

• Manually Type Desired Text for Each Label
• Manually Link Each Label to Cell with Desired Text
• Use the Chart Labeler Program
• Use Values from Cells (Excel 2013 and later)
• Write Your Own VBA Routines

## Manually Type Desired Text for Each Label

The least sophisticated way to get your desired text into each label is to manually type it in.

Click once on a label to select the series of labels.

Click again on a label to select just that specific label.

Double click on the label to highlight the text of the label, or just click once to insert the cursor into the existing text.

Type the text you want to display in the label, and press the Enter key.

Repeat for all of your custom data labels. This could get tedious, and you run the risk of typing the wrong text for the wrong label (I initially typed “alpha” for the label above, and had to redo my screenshot).

One thing that makes this approach unsophisticated is that the typed labels are not dynamic. If th text in one of the cells changes, the corresponding label will not update.

## Manually Link Each Label to Cell with Desired Text

Select an individual label (two single clicks as shown above, so the label is selected but the cursor is not in the label text), type an equals sign in the formula bar, click on the cell containing the label you want, and press Enter. The formula bar shows the link (=Sheet1!\$D\$3).

Repeat for each of the labels. This could get tedious, but at least the labels are dynamic. If the text in one of the cells changes, the corresponding label updates to show the new text.

## Use the Chart Labeler Program

Brilliant Excel jockey and former MVP Rob Bovey has written a Chart Labeler add-in, which allows you to assign labels from a worksheet range to the points in a chart. It is free for anyone to use and can be downloaded from http://appspro.com. Rob colls it the XY Chart Labeler, but it actually works with any chart type that supports data labels.

When installed, the add-in adds a custom ribbon tab with a handful of useful commands. The tab is added at the end of the ribbon, but being pressed for space I moved it digitally to the beginning.

With a chart selected, click the Add Labels ribbon button (if a chart is not selected, a dialog pops up with a list of charts on the active worksheet). A dialog pops up so you can choose which series to label, select a worksheet range with the custom data labels, and pick a position for the labels.

If you select a single label, you can see that the label contains a link to the corresponding worksheet cell. This is like the previous method, but less tedious and much faster.

## Use Values from Cells (Excel 2013 and later)

After years and years of listening to its users begging, Microsoft finally added an improved labeling option to Excel 2013.

First, add labels to your series, then press Ctrl+1 (numeral one) to open the Format Data Labels task pane. I’ve shown the task pane below floating next to the chart, but it’s usually docked off to the right edge of the Excel window.

Click on the new checkbox for Values From Cells, and a small dialog pops up that allows you to select a range containing your custom data labels.

Then uncheck the Y Value option. I also uncheck the Show Leader Lines option, which is another enhancement added in Excel 2013. Leader lines are hardly ever useful for the charts I make, but many users are happy with them.

While these data labels are not explicitly linked to worksheet cells as in the previous approaches, they still reflect any changes to the cells that contain the labels.

## Write Your Own VBA Routines

I’ve put together a couple little routines that help with data point labeling. These are quick and dirty, because sometimes that’s all that you need. Also, writing your own code allows you to streamline your workflow according to your specific requirements.

### Add Data Labels from Range Selected by User

This routine first makes sure a chart is selected, then it determines which series is to be labeled. It asks the user to select a range using an InputBox, and if the user doesn’t cancel it adds a label to the series point by point, linking the label to the appropriate cell.

``````Sub AddLabelsFromUserSelectedRange()
Dim srs As Series, rng As Range, lbl As DataLabel
Dim iLbl As Long, nLbls As Long

If Not ActiveChart Is Nothing Then
If ActiveChart.SeriesCollection.Count = 1 Then
' use only series in chart
Set srs = ActiveChart.SeriesCollection(1)
Else
' use series associated with selected object
Select Case TypeName(Selection)
Case "Series"
Set srs = Selection
Case "Point"
Set srs = Selection.Parent
Case "DataLabels"
Set srs = Selection.Parent
Case "DataLabel"
Set srs = Selection.Parent.Parent
End Select
End If

If Not srs Is Nothing Then
' ask user for range, avoid error if canceled
On Error Resume Next
Set rng = Application.InputBox( _
"Select range containing data labels", _
"Select Range with Labels", , , , , , 8)
On Error GoTo 0

If Not rng Is Nothing Then
' point by point, assign cell's address to label
nLbls = srs.Points.Count
If rng.Cells.Count < nLbls Then nLbls = rng.Cells.Count
For iLbl = 1 To nLbls
srs.Points(iLbl).HasDataLabel = True
Set lbl = srs.Points(iLbl).DataLabel
With lbl
.Position = xlLabelPositionRight
End With
Next
End If
End If
End If
End Sub``````

### Add Data Labels from Row or Column Next to Y Values

This routine first makes sure a chart is selected, then it determines which series is to be labeled. It doesn’t bother the user, instead the routine parses the series formula to find the range containing the Y values, and if this is a valid range, it finds the next column or row, depending on the orientation of the Y values range. The code then adds a label to the series point by point, linking the label to the appropriate cell.

``````Sub AddLabelsFromRangeNextToYValues()
Dim srs As Series, rng As Range, lbl As DataLabel
Dim iLbl As Long, nLbls As Long
Dim sFmla As String, sTemp As String, vFmla As Variant

If Not ActiveChart Is Nothing Then
If ActiveChart.SeriesCollection.Count = 1 Then
' use only series in chart
Set srs = ActiveChart.SeriesCollection(1)
Else
' use series associated with selected object
Select Case TypeName(Selection)
Case "Series"
Set srs = Selection
Case "Point"
Set srs = Selection.Parent
Case "DataLabels"
Set srs = Selection.Parent
Case "DataLabel"
Set srs = Selection.Parent.Parent
End Select
End If

If Not srs Is Nothing Then
' parse series formula to get range containing Y values
sFmla = srs.Formula
sTemp = Mid\$(Left\$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1)
vFmla = Split(sTemp, ",")
sTemp = vFmla(LBound(vFmla) + 2)
On Error Resume Next
Set rng = Range(sTemp)

If Not rng Is Nothing Then
' use next column or row as appropriate
If rng.Columns.Count = 1 Then
Set rng = rng.Offset(, 1)
Else
Set rng = rng.Offset(1)
End If

' point by point, assign cell's address to label
nLbls = srs.Points.Count
If rng.Cells.Count < nLbls Then nLbls = rng.Cells.Count
For iLbl = 1 To nLbls
srs.Points(iLbl).HasDataLabel = True
Set lbl = srs.Points(iLbl).DataLabel
With lbl
.Position = xlLabelPositionRight
End With
Next
End If
End If
End If
End Sub``````

## Axis Labels That Don’t Block Plotted Data

Someone on Twitter asked me how to make axis labels stay on the side of the axis away from the data. This bar chart illustrates the desired outcome. When bars indicate positive values, the labels are on the negative side of the axis, and vice versa.

## The Problem

This data and chart show the behavior of axis labels in Excel. Even though some data is negative, the labels are always on the negative side of the axis, where the labels overlap with the bars. Depending on the series fill color, this could make the labels illegible.

### Lame Attempts

You could play with axis formats to try to make things better. You could format the horizontal axis to make the vertical axis cross at value other than zero; I used -25 below left. This is a terrible solution, though, because now the bars don’t start at the baseline of zero, and even the negative values are plotted with positive direction bars. No good.

You could format the vertical axis labels to make them appear in the Low position, that is, below the lowest values on the horizontal axis, below right. In many cases this is appropriate, but it’s not ideal.

## The Solution

The trick is to realize that you can’t make the built-in axis labels do what you want, then think of another way to show the labels. In this case we’ll add a hidden bar chart series, which will use its own data labels.

I’ve added a column with small negative values next to positive plotted values, and small positive values next to negative plotted values.

When you add the new data to the chart, the built-in axis labels still overlap with the negative bars (left). The bars are offset, but we can fix this by formatting either of the bars and changing the overlap to 100% (right).

I hid the built-in axis labels (left). Then I added data labels to the added series (right). The default labels show the plotted values (10 or -10), and I selected the inside base position for the labels.

The charts below show the four positions for data labels in clustered column and bar charts. Center means in the center of the bars. Inside Base means inside the bar next to the base (bottom) of the bar (next to the axis). Inside End and Outside End mean inside and outside the far end of the bar. Stacked charts can’t have Outside End labels, because these would overlap with any bar stacked on top of the one being labeled.

I’ve formatted the labels so that they display the category names instead of the values (left) Then I hid the dummy bar chart series by setting its fill to No Fill (right).

Data labels are not allowed to be as long as axis labels: note that the Omicron label is wrapped onto two lines. For general clarity it’s a good idea to keep your labels short, but here it’s doubly important.

Excel 2013 has finally enabled users to change the size of data labels (but not yet chart and axis titles). However, I’m using Excel 2010 right now, so I don’t have that luxury.

Since the data label size is related to the chart dimensions, you can make the label fit onto one line by stretching the chart.

If your chart size is constrained, you can change the font instead. Below left I’ve changed the Calibri labels from 10 to 9 points. Below right I’ve changed from Calibri 10 points to Arial 8 points.

For this chart, I think I’ll keep Calibri 9 point labels.

## Highlight a Specific Data Label in an Excel Chart

I was asked recently whether it was possible to change the font color of a data label in an Excel chart to highlight the maximum value.

Well, sure, anything is possible. And there are at least two ways to accomplish this task. Because I’ve been doing a lot of coding lately, my first thought was an approach using VBA. Then of course I came to my senses, and worked out a non-programmatic approach. If possible, it’s usually advantageous not to rely on VBA for such tasks.

## The VBA Approach

Because I thought of it first, I’ll show the VBA method of formatting the label for the maximum value first.

Here is the simple data and chart, with all labels formatted with white text.

For this simple example, I want the tallest bar to have a black label, like this:

Here is the simple VBA routine I worked out to accomplish my task.

``````Sub HighlightMaxDataLabel()
Dim srs As Series
Dim vY As Variant
Dim iPt As Long, nPts As Long
Dim dMax As Double
Dim iHighlightColor As Long

' do nothing if user hasn't selected a chart
If Not ActiveChart Is Nothing Then
Set srs = ActiveChart.SeriesCollection(1)

' highlight color: change to suit
iHighlightColor = RGB(0, 0, 0)

' reset all labels to original font color
With srs.DataLabels.Font
.Color = .Color
End With

vY = srs.Values
nPts = srs.Points.Count

' find maximum value
dMax = vY(1)
For iPt = 2 To nPts
If dMax < vY(iPt) Then
dMax = vY(iPt)
End If
Next

For iPt = 1 To nPts
' highlight all labels at maximum value
If vY(iPt) = dMax Then
srs.Points(iPt).DataLabel.Font.Color = iHighlightColor
End If
Next

End If

End Sub``````

When the data changes, the labels don’t immediately change.

Run the code again, and the labels are now properly highlighted.

Of course you could modify the code and stick it into a Worksheet_Change event procedure to make the labels change when the data changes.

I made sure that the code checked all values, without stopping at the first maximum. This way, both labels are highlighted if there’s a tie for first.

## The Non-Programmatic Approach

Here is the same data with a couple extra columns, and the column chart without data labels. The added columns provide data for hidden line chart series which will contain the differently formatted data labels.

Assuming the data is in A1:D6, the formulas are:

Cell C2: `=IF(B2<MAX(B\$2:B\$6),B2,NA())`

Cell D2: `=IF(B2=MAX(B\$2:B\$6),B2,NA())`

These formulas result in only one of the two line chart series having a marker for each column of the column chart.

Start by making the chart using all of the data (left), or if you’ve already got the chart, add the extra series. Then change the chart type of the additional series* to line chart (right).

* right click on the series, choose Change Series Chart Type from the pop up menu, and select the desired chart type.

Add data labels to each line chart* (left), then format them as desired (right).

* right click on the series, choose Add Data Labels from the pop up menu.

Finally format the two line chart series so they use no line and no marker.

When the data change, the chart labels change just as quickly as Excel can calculate the new values in columns C and D. No need to hassle with VBA event procedures.

If more than one value matches the maximum, each will be highlighted as the maximum.