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:

Data to plot along an 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.

Expanded data and preliminary chart

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.

Evolution of the chart - step 2

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.

Evolution of the chart - step 3

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.

Evolution of the chart - step 4

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.

Evolution of the chart - step 2

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.

Finished chart

 

Peltier Tech Chart Utility

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.

Simple Chart with Custom Data Labels

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.

Simple Chart with Default Data Labels

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.

Simple Chart with Data Labels Selected

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

Simple Chart with Specific Data Label Selected

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

Simple Chart with Data Label Text Selected

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

Simple Chart with Data Label Text Being Typed

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

Simple Chart with Data Label Link Being Entered

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.

XY Chart Labeler Ribbon Tab

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.

XY Chart Labeler Dialog

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.

XY Chart Labeler Dialog

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.

Format Data Labels Task Pane

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.

Format Data Labels Task Pane

Select your data label range.

Format Data Labels Task Pane

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.

Format Data Labels Task Pane

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
            .Text = "=" & rng.Cells(iLbl).Address(External:=True)
            .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
            .Text = "=" & rng.Cells(iLbl).Address(External:=True)
            .Position = xlLabelPositionRight
          End With
        Next
      End If
    End If
  End If
End Sub

 

 

Peltier Tech Chart Utility

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.

Axis labels that do not block the data

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.

Default axis labels overlap negative data

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.

Workarounds to prevent axis labels from obscuring the data

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.

Data for dummy axis label series

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

Adding custom axis labels

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.

Adding custom axis 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.

Data label positions in Excel bar and column charts

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

Adding custom axis labels

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.

Axis labels that do not block the data

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.

Axis labels that do not block the data

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

 

Peltier Tech Chart Utility

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.

Highlight Max Data Label with VBA - Data and Chart 1

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

Highlight Max Data Label with VBA - Data and Chart 2

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.

Highlight Max Data Label with VBA - Data and Chart 3

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

Highlight Max Data Label with VBA - Data and Chart 4

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.

Highlight Max Data Label with VBA - Data and Chart 5

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.

Highlight Max Data Label without VBA - Data and Chart 1

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.

Highlight Max Data Label without VBA - Charts 2

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.

Highlight Max Data Label without VBA - Charts 3

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

Highlight Max Data Label without VBA - Data and Chart 4

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.

Highlight Max Data Label without VBA - Data and Chart 5

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

Highlight Max Data Label without VBA - Data and Chart 6

 

 

Peltier Tech Chart Utility

Peltier Tech Charts for Excel 3.0

 

Create Excel dashboards quickly with Plug-N-Play reports.