VBA: Assign Names to Chart Series

If you select a well-defined worksheet range and insert a chart, Excel parses the range and assigns values (Y values), categories (X values), and series names based on its analysis of the range.

For example, if you select the range C2:F8 shown below, Excel notices that the top left cell C2 is blank, so Row 2 and Column C will be treated differently. Excel also notices that there are more rows than columns, so the data will be plotted by column, with categories in the first column and series names in the first row. When you insert a chart, the worksheet is highlighted showing that Column C (purple highlights) is used as category labels (X values), Row 2 (red highlights) is used as series names, and the rest of the area (blue highlights) is used as Y values.

Nice Data Leads to a Nice Chart

If you always arrange your data nicely like this, you will rarely have to fix up what Excel uses for X values, Y values, and series names.

Assign Names from Cells in Front of Y Values

But sometimes your chart hasn’t been no nicely assigned to the worksheet range. The chart below has series plotted by row, but the first column has not been used for series names. Instead the series have the dreaded “Series1″, “Series2″, etc., as their names.

Chart plotted by row with no series names

Similarly, this chart has series plotted by column, but the first row has not been used for series names. Again, the series are labeled with the dreaded “Series1″, “Series2″, etc.

Chart plotted by column with no series names

You can manually name the series, using the Select Data command from the ribbon or from the right click menu, or editing the series formula. But it’s not too much trouble to write a little code to find the appropriate cells to name the series in a chart.

I’ll start with a routing that works on one chart series. It gets the series formula, parses out the argument that specifies the series Y values, and finds the range containing those values. (If the Y values are not from a range, for example, they are hard-coded as an array right in the formula, the code skips the series.) If the Y values range is a row, the code finds the cell to the left of this range in the same row; if the Y values range is a column, the code finds the cell above this range in the same column. This cell is used to create a link for the series name.

Sub Series_AssignNameToCellBeforeYValues(srs As Series)
  Dim sFmla As String
  Dim vFmla As Variant
  Dim sYVals As String
  Dim rYVals As Range
  Dim rName As Range
  
  sFmla = srs.Formula
    ' e.g. =SERIES("Name",Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,1)
  sFmla = Replace(sFmla, "=SERIES(", "")
  sFmla = Left$(sFmla, Len(sFmla) - 1)
    ' e.g. "Name",Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,1
  vFmla = Split(sFmla, ",")
  
  If UBound(vFmla) + 1 - LBound(vFmla) = 4 Then
    sYVals = vFmla(LBound(vFmla) + 2)
      ' third element (first + 2)
      ' e.g. Sheet1!$B$2:$B$10
    On Error Resume Next
    Set rYVals = Range(sYVals)
    On Error GoTo 0
    If Not rYVals Is Nothing Then
      If rYVals.Cells.Count > 1 Then
        On Error Resume Next
        If rYVals.Columns.Count > rYVals.Rows.Count Then
          ' by row, take cell to left
          Set rName = rYVals.Resize(1, 1).Offset(, -1)
        Else
          ' by col, take cell above
          Set rName = rYVals.Resize(1, 1).Offset(-1)
        End If
        On Error GoTo 0
        If Not rName Is Nothing Then
          srs.Name = "=" & rName.Address(, , , True)
            ' e.g. "=Sheet1!$B$1"
            ' use formula notation so it links to cell
        End If
      End If
    End If
  End If
End Sub

That’s great for one series. Use the following code to call the above procedure for each series in a given chart:

Sub Chart_AssignNameToCellBeforeYValues(cht As Chart)
  Dim srs As Series
  For Each srs In ActiveChart.SeriesCollection
    Series_AssignNameToCellBeforeYValues srs
  Next
End Sub

Use this to assign names for the active chart:

Sub ActiveChart_AssignNameToCellBeforeYValues()
  If Not ActiveChart Is Nothing Then
    Chart_AssignNameToCellBeforeYValues ActiveChart
  End If
End Sub

Use this to assign names to every chart in the active sheet:

Sub AllCharts_AssignNameToCellBeforeYValues()
  Dim chtob As ChartObject
  For Each chtob In ActiveSheet.ChartObjects
    Chart_AssignNameToCellBeforeYValues chtob.Chart
  Next
End Sub

Here is our first chart, plotted by row, with its series names now defined: note the red highlighted range containing the same labels now displayed in the legend.

Chart plotted by row with assigned series names

Here is the second chart, plotted by column, with names assigned.

Chart plotted by column with assigned series names

In fact, the alignment of all series need not be the same, since the routine works series-by-series. The top chart below uses the first two rows of data from the first chart above and the last two columns from the second chart, with no series names assigned. The bottom chart shows how the code has correctly assigned names.

Charts with mixed rows and columns, without and with series names

Assign Names from an Arbitrary Range

What if the series names are in a range that isn’t connected to the rest of the chart data? Here I want to use series names from the range below the chart. The highlights in the worksheet only show X and Y values for the selected series.

Chart with no series names

Here is a routine that assigns names from a given range to series in a given chart:

Sub Chart_AssignNamesFromRange(cht As Chart, rng As Range)
  Dim iSrs As Long
  For iSrs = 1 To cht.SeriesCollection.Count
    If iSrs > rng.Cells.Count Then Exit For
    cht.SeriesCollection(iSrs).Name = _
        "=" & rng.Cells(iSrs).Address(, , , True)
  Next
End Sub

This routine asks the user to select a range, then calls the routine above to assign names from this range to the active chart:

Sub ActiveChart_AssignNamesFromRange()
  Dim myRange As Range
  On Error Resume Next
  Set myRange = Application.InputBox( _
      "Select a range containing series names for the active chart.", _
      "Select Range", , , , , , 8)
    ' Type 8 to input a range
  If Not myRange Is Nothing Then
    Chart_AssignNamesFromRange ActiveChart, myRange
  End If
End Sub

Here is the dialog, which I’ve already used to select a range containing labels.

Select a range dialog

Here the chart’s series have been labeled. The highlights show X and Y values and a series name for the selected series.

Chart with series names from an arbitrary range

Peltier Tech Chart Utility

Reciprocal Chart Axis Scale

Many physical phenomena exhibit behavior that shows faster rates or greater solubilities at higher temperatures. An Arrhenius equation gives the following relationship between some measure of reaction rate or chemical solubility and temperature:

K = A exp (-Q/RT)

where K is the rate or solubility, A is a constant, Q is an activation energy, R is the gas constant, and T is the absolute temperature. The equation above can be restated to:

log (K) = A' - Q/RT

A chart can be constructed with log(K) on the Y axis and reciprocal temperature (1/T) on the X axis; the slope of this line indicates the activation energy (actually -Q/R) and the intercept is the new constant A’. This page describes the construction of one variation of this type of chart.

Arrhenius Plot

Microsoft Excel does not offer a built in capability to chart reciprocal data, but the technique described here allows you to simulate a reciprocal scale along a chart axis (the horizontal axis in this example). A dummy series is plotted along the X axis, and formatted to look like an axis, with error bars as customized vertical gridlines and data labels as customized axis tick labels.

In addition, we will use a dummy series along the Y axis to display selected minor tick mark labels.

Data

The tables below show the dummy experimental data (shaded blue), along with the data I used to construct the simulated X axis (shaded orange) and Y axis (shaded green) in this example. The only significance of this data is that I chose it to fall roughly on a straight line on the log K vs. 1/T chart. This line has a positive slope because the reciprocal temperature increases from right to left (temperature decreases from left to right).

The temperatures in column F are numbers with a custom number format of “0°C”. The reciprocal temperatures in columns C and G were defined as possible: For a Celsius temperature in cell B4, the formula in cell C4 is:

=1000/(B4+273)

Dummy Horizontal Axis

Reciprocal temperatures (Column G) are used for the X values, but the original Celsius temperatures (Column F) are used for the X axis labels, because they are more meaningful.

Y values (Column H) correspond to the minimum on the Y axis (it will eventually be a logarithmic axis with a min of 1 and max of 100).

Dummy Vertical Axis

X values for the Dummy Vertical Axis correspond to the maximum on the X axis (Column J, X=3.9526), not the minimum, because the values are charted in reverse order.

The Y axis labels (Column K) were chosen to show selected minor tick labels, to supplement the built-in major tick labels (1, 10, and 100).

The Chart

This is an XY Scatter plot of the Rate Y (Column D) as a function of Temperature X (Column B). This is not the chart we will start with.

We will start with this XY Scatter plot of Rate Y (Column D) as a function of Reciprocal Temperature X (Column C).

First I’ll construct the custom X axis labels and vertical gridlines, then I’ll add custom Y axis minor tick labels. I’m using the same light gray color for all axis lines and gridlines in the chart.

Horizontal (Reciprocal) Axis Construction

We want temperature to increase from left to right, so reciprocal temperature should increase from right to left. To accomplish this, format the horizontal axis, and select the Values in Reverse Order option.

We want the vertical axis on the left, so while formatting the horizontal axis, select Axis Crosses at Maximum Axis Value.

We want the horizontal axis to go from -20°C to 240°C (on a reciprocal basis), so set the min to 1.9493 and the max to 3.9526. The major spacing doesn’t matter, because we will not be using the built-in axis labels or vertical gridlines.

Select the default vertical gridlines and press Delete.

Format the horizontal axis, and for Label Position, select the none option.

Copy the dummy horizontal axis data from columns G and H, select the chart, and use Paste Special from the Home tab of the ribbon to add the copied data as a new series. These are the orange circles near the bottom of the chart.

We’ll use error bars for the chart’s vertical gridlines. Select the dummy horizontal axis data points, and add error bars using the plus icon next to the chart (Excel 2013) or the Chart Tools > Layout tab (Excel 2007-2010). Since it’s an XY Scatter chart series, it gets both horizontal and vertical error bars.

Delete the horizontal error bars. Format the vertical error bars using the Plus direction only and without end caps, with a value of 99 (the Y maximum of 100 minus the Y minimum of 1), and using the light gray line color.

Right click the dummy horizontal data series and select Add Data Labels. They were added above the points, and I’ve used the same orange as the font color to clarify this tutorial.

Several techniques for using custom labels as chart series data labels are described in Apply Custom Data Labels to Charted Points elsewhere on this blog. I’ve used Excel 2013’s Value From Cells label option, using the Celsius temperatures in column F.

Change the data label position from above to below the points.

Change the alignment of the data labels to “Rotate Text Up” on the Home tab of the ribbon or “Rotate 270°” in the Format Data Labels dialog.

Finally, resize the plot area to increase the margin between the plot area and the bottom of the chart. This allows the axis title and data labels to appear without overlapping.

The custom horizontal axis is now finished. Hide the last evidence of your trickery by reformatting the dummy axis series to show no markers.

Vertical (Logarithmic) Axis Construction

Start fixing up the vertical axis by formatting it, and selecting Logarithmic Scale, and keep the default base 10. The error bar/gridlines now span the entire height of the chart.

Add minor horizontal gridlines and format them with the same line color as the other gridlines and axes.

Copy the green-shaded data for the dummy vertical axis series from columns J and K, select the chart, and use Paste Special to add the data as a new series.

Add data labels to this new series to the left of the data points, using the default Y value labels. These new labels are shown in green in the chart below. The labeling of the vertical axis is now complete.

Format the vertical dummy axis series to use no markers, hiding any evidence of your axis deception.

Here is the finished chart, with all labels in the same dark gray font color.

Extension to Other Custom Axes

The technique shown here to add gridlines and labels corresponding to a reciprocal temperature scale can be applied to any arbitrary scale type, limited only by your ability to transform the desired scale into a plain vanilla linear axis. You could generate a Probability Scale Axis, or put Category Labels Along a Vertical Axis, or apply more Conventional Scientific Notation Axis labels.

Peltier Tech Chart Utility

Bullet Charts in Excel (updated and simplified)

People have struggled for a long time to find effective means of displaying single points of data. With the recent obsession with dashboard displays of business data, this struggle has come to a head. On one side, there are myriad gauge designs; on the other are more effective charts, notably Stephen Few’s Bullet Graphs. In this tutorial I’ll show how to make bullet graphs in Excel, using a simplified approach that works for horizontal and vertical bullet charts.

Gauges

Gauge

Gauge graphs are based on the numerous dials found in automobile dashboards and aircraft cockpits. These dials make sense in a mechanical setting: they are activated by small voltages generated by sensors in the equipment. In the gauge, this small voltage results in rotation of a needle around a pivot. These gauges are reliable and inexpensive, compared to comparable linear electromechanical gauges. These dials also make sense in a cockpit: you need to know what’s happening right now, and in general you do not need to compare the values on several dials.

Some business dashboard designers have missed the point. Instead of treating a dashboard as a place where a lot of information is displayed in a limited field of view, they apply the metaphor of cockpit too literally, as if the manager is driving his desk around the company. Dial gauges in a business dashboard are remarkably ineffective. They take up a lot of space to show very little information, and comparisons among several gauges are difficult to judge with any precision. They also show only a “right now” value, rather than a historical view afforded by line charts. It seems that more design effort has gone into making them gauge-like rather than making them comprehensible.

Yet so-called Business Intelligence dashboards are loaded with gauges. BI graphics packages are full of them, each more glossy and glittery than the next.

Bullet Graphs

Bullet Graph

To address many of the problems with gauges, Stephen Few started with a simple bar chart and developed Bullet Graphs. Bullet graphs require less space than gauges to show the same amount of information, and their linear style makes it easy to compare values on adjacent bullet graphs. Stephen describes bullet graphs in Bullet Graphs for Not-to-Exceed Targets and has published a Bullet Graph Design Specification.

Vertical Single Bullet

A bullet chart requires five values: high, medium, and low background values, a target, and an actual value, as in the table below. Insert a clustered column chart, and make the chart narrow or adjust it later (middle); in most cases you won’t need to go to the extra effort of making a stacked column chart. Excel assumes that one column of data should be plotted as a single series, so click the Switch Row/Column button on the Chart Tools > Design ribbon tab (right). Format High, Medium, and Low as light to dark gray bars. I’ve formatted Actual as a dark blue bar (which will show up slightly better on screen than a black bar), and Target as a red bar, but these formats will have to be recreated since their chart types will be changed.

Right click the Actual series, and choose Change Series Chart Type. Select the Line chart type with markers, format its marker as a square with dark blue border and fill, and format it to use No Line (left chart below). Right click the Target series, and choose Change Series Chart Type. Select the XY chart type with markers and no lines, and choose the dash marker using red fill and a red border with thickness of 1.5 instead of the default 0.75, which will eventually look better in the legend (middle chart below). Since Excel automatically plots Actual on the secondary axis, format the series to appear on the primary axis (below right).

Note that all of the above three steps can be accomplished in Excel 2013 in one trip to the Chart Type dialog, but earlier versions need three different formatting steps.

Format any of the remaining column chart series (High, Medium, or Low) and set the Overlap to 100 and Gap Width of 50 (below left). Add a legend (middle). For this thin chart, Excel may make both the chart and the legend too narrow, so adjust the width of the plot area and the legend so both fit nicely (below right).

Select the Actual series and add error bars. It’s a line chart series, so you only get vertical error bars (first chart below). Format the error bars to show the minus direction only with a percentage of 100, without end caps, using the same dark blue for the line, and make the line thick (such as 7.5 points in the second chart below).

Select the Target series and add error bars. It’s an XY scatter chart series, so you get horizontal and vertical error bars (third chart below). Delete the vertical error bars, and format the horizontal error bars to show both directions, at a value of 0.3, without end caps, with a 1.5-point red line.

Sometimes the default error bars are too short to see, so you need to use the dropdown in the top left of the Chart Tools > Format or Layout tab to select the set of error bars you need to format.

Now comes a little bit of Excel Chart Magic. Error bars do not show up in the legend, so the dark blue and red symbols for Actual and Target would disappear. But we went to the trouble of formatting our markers to look good in the legend.

If we format individual points in a series differently than we format the entire series, the formatting for the entire series stays in the legend. We need to format the individual Actual and Target points differently, by selecting the individual points and choosing No Marker. Here’s how:

Click on the marker for Actual, which selects the whole Actual series. Then click on the marker again or click the right keyboard arrow key, which selects just the single point. Now press Ctrl+1 to format the point. If the title of the format dialog or task pane says “Format Data Point”, you are ready to go. If it says “Format Data Series”, you need to try again to select just the data point. In the Format Data Point dialog, choose No Marker for Marker Style. This hides the Actual marker for the data point in the chart, but leaves the marker in the Actual legend entry.

Repeat the single click plus single click technique to select the marker for Target. In the Format Data Point dialog, again choose No Marker for Marker Style. This hides the marker for the data point in the chart, but leaves the marker in the legend.

The result is the left chart below. Of course, if you don’t need a legend, you could have skipped all the marker formatting we did before, not shown a legend at all, and made the bullet chart even slimmer (below right).

Vertical Multiple Bullet

The protocol for multiple vertical bullets in a single chart is the same as for a single bullet, with minor adjustments. You need one column per bullet, with High, Mid, and Low, plus Actual and Target for each. The High, Mid, Low, and Target can be the same or different for each bullet. From the data below left, insert a clustered column chart (below middle), and if necessary switch rows and columns so each category (Alpha, Beta, and Gamma below) has its own bullet components.

Right click the Actual series, and choose Change Series Chart Type. Select the Line chart type with markers, format its marker as a square with dark blue border and fill (left chart below). This messes up the legend, but remembering to set the line to No Line fixes it (below right)

Right click the Target series, and choose Change Series Chart Type. Select the XY chart type with markers and no lines, and choose the dash marker using red fill and a red border with thickness of 1.5 instead of the default 0.75, which will eventually look better in the legend (left chart below). Since Excel automatically plots Actual on the secondary axis, format the series to appear on the primary axis (below middle). Format any of the remaining column chart series (High, Medium, or Low) and set the Overlap to 100 and Gap Width of 50 (below right).

Select the Actual series and add error bars. It’s a line chart series, so you only get vertical error bars (left chart below). Format the error bars to show the minus direction only with a percentage of 100, without end caps, using the same dark blue for the line, and make the line thick (such as 7.5 points in the right chart below).

Select the Target series and add error bars. It’s an XY scatter chart series, so you get horizontal and vertical error bars (left chart below). Delete the vertical error bars, and format the horizontal error bars to show both directions, at a value of 0.3, without end caps, with a 1.5-point red line (below right).

Now we repeat our little bit of Excel Chart Magic.

Click on the Actual series, which selects the whole series. Then click on the first marker or click the right keyboard arrow key, which selects just the first point. Now press Ctrl+1 to format the point. If the title of the format dialog or task pane says “Format Data Point”, choose No Marker for Marker Style. Click the right arrow key to select the next point, and again choose the No Marker option. Repeat for the rest of the Actual points. This hides the Actual marker for the points in the chart, but leaves the marker in the Actual legend entry.

Click on the Target series, then click on the first Target marker or click the right arrow key to select the first marker for Target. In the Format Data Point dialog, choose No Marker for Marker Style. Click the right arrow key to select the next Target point, and again choose the No Marker option, and repeat for the rest of the Target data points. This hides the marker for the points in the chart, but leaves the marker in the legend.

The result is the left chart below. Again, if you don’t need a legend, you could have skipped all the marker formatting we did before, omitted the legend, and made the bullet chart narrower (below right).

Single Horizontal Bullet

The protocol for a horizontal bullet is similar to that for a vertical bullet, with adjustments to account for different axis behavior in a horizontal bar chart. First, one more row of data is needed for the Y value of 0.5 for plotting Actual and Target (below left). Select only the blue shaded range (not the entire data range as before) and insert a clustered horizontal bar chart (below right)

Select the cells containing the Actual label and value, then hold Ctrl and select the cells containing the Y label and value, so both areas are highlighted, and copy (Ctrl+C). Select the chart, and use Paste Special from the Home ribbon tab to paste the data as a new chart series, using values in rows, series names in first column, and categories in first row.

The result is the barely visible bar at the top of the chart, below left. Change the series name from Y to Actual, either by using the Select Data dialog, by selecting the series and dragging the series name highlight from the cell containing Y to the cell containing Actual, or by editing the series formula to put the proper cell reference in the first argument of the formula (below right). Right click the Actual series, choose Change Series Chart Type, and select the XY Scatter option with markers and no line, and format it as a marker with dark blue border and fill (bottom middle).

Select the cells containing the Target label and value and the Y label and value, and copy (Ctrl+C). Select the chart, and use Paste Special to paste the copied data as a new chart series, using values in rows, series names in first column, and categories in first row. This is pasted as another XY Scatter series, again with the name Y. Format the series using the dash marker with a 1.5 point red line and red fill (below left). Change the series name to Target (below right).

Format any of the remaining column chart series (High, Medium, or Low) and set the Overlap to 100 and Gap Width of 50 (below left). Format the secondary vertical axis (right edge of the chart) so the minimum and maximum are manually fixed at 0 and 1, the axis tick label setting is No Labels, and the line option is No Line (below right). Format the horizontal axis so the tick label spacing is more frequent (bottom middle).

Select the Actual series and add error bars. It’s an XY scatter chart series, so you get horizontal and vertical error bars (top left chart below). Delete the vertical error bars. Format the horizontal error bars to show the minus direction only with a percentage of 100, without end caps, using the same dark blue for the line, and make the line thick (such as 6 points in the top right chart below).

Select the Target series and add error bars. As an XY scatter chart series, it gets horizontal and vertical error bars (bottom left chart below). Delete the horizontal error bars, and format the horizontal error bars to show both directions, at a value of 0.3, without end caps, with a 1.5-point red line (bottom right chart below).

Time to work our Excel Chart Magic again. Click on the marker for Actual to select the whole series. Then click on the marker again or click the right keyboard arrow key, which selects just the single point, and press Ctrl+1 to format the point. In the Format Data Point dialog, choose No Marker for Marker Style, which hides the Actual marker for the data point in the chart, but leaves the marker in the Actual legend entry.

Repeat the single click plus single click technique to select the marker for Target. In the Format Data Point dialog, again choose No Marker for Marker Style, to hide the marker for the data point in the chart, but leave the marker in the legend.

The result is the left chart below. If you don’t need a legend, you could have skipped all this finagling with markers, not shown a legend, and made the bullet chart thinner (below right).

Multiple Horizontal Bullet

The protocol for multiple horizontal bullets in a single chart is the similar to that for a single bullet, with minor adjustments. You need one column of data per bullet, with High, Mid, and Low, plus Actual and Target for each.  Also, a row of data is needed for the Y values for plotting Actual and Target (below left). These values are 0.5 for the first category (Alpha), 1.5 for the next (Beta), and so on, adding 1 to each previous Y value.

Select only the blue shaded range (not the entire data range as before) and insert a clustered horizontal bar chart (top right below). The categories (Alpha, Beta, and Gamma) are plotted bottom-to-top, in a phenomenon described in Excel Plotted My Bar Chart Upside-Down. This is normal behavior, of course, and it’s simple to counteract by formatting the vertical axis to plot categories in reverse order, with the horizontal axis crossing at the maximum category (bottom right below).

Select the cells containing the Actual label and values, then hold Ctrl and select the cells containing the Y label and values, so both areas are highlighted, and copy (Ctrl+C). Select the chart, and use Paste Special from the Home ribbon tab to paste the data as a new chart series, using values in rows, series names in first column, and categories in first row.

The result is the barely visible bar at the top of the chart, below left. Change the series name from Y to Actual (below right). Right click the Actual series, choose Change Series Chart Type, and select the XY Scatter option with markers and no line, and format it as a marker with dark blue border and fill (bottom middle).

Select the cells containing the Target label and values and the Y label and values, and copy (Ctrl+C). Select the chart, and use Paste Special to paste the copied data as a new chart series, using values in rows, series names in first column, and categories in first row. This is pasted as another XY Scatter series, again with the name Y. Format the series using the dash marker with a 1.5 point red line and red fill (below left). Change the series name to Target (below right).

Format any of the remaining column chart series (High, Medium, or Low) and set the Overlap to 100 and Gap Width of 50 (below left). Format the secondary vertical axis (right edge of the chart) so the minimum and maximum are manually fixed at 0 and the number of categories (3 for Alpha, Beta, and Gamma), the axis tick label setting is No Labels, and the line option is No Line (below left). Format the horizontal axis so the tick label spacing is more frequent (bottom middle).

Select the Actual series and add error bars. It’s an XY scatter chart series, so you get horizontal and vertical error bars (top left chart below). Delete the vertical error bars. Format the horizontal error bars to show the minus direction only with a percentage of 100, without end caps, using the same dark blue for the line, and make the line thick (such as 6 points in the top right chart below).

Select the Target series and add error bars. As an XY scatter chart series, it gets horizontal and vertical error bars (bottom left chart below). Delete the horizontal error bars, and format the horizontal error bars to show both directions, at a value of 0.3, without end caps, with a 1.5-point red line (bottom right chart below).

Time to disappear the Actual and Target markers from the chart but keep them in the legend. Click on the marker for Actual to select the whole series. Then click on the marker again or click the right keyboard arrow key, which selects just the single point, and press Ctrl+1 to format the point. In the Format Data Point dialog, choose No Marker for Marker Style, which hides the Actual marker for the data point in the chart, but leaves the marker in the Actual legend entry.

Repeat the single click plus single click technique to select the marker for Target. In the Format Data Point dialog, again choose No Marker for Marker Style, to hide the marker for the data point in the chart, but leave the marker in the legend.

The result is the left chart below. If you don’t need a legend, you could have skipped all this finagling with markers, not shown a legend, and made the bullet chart thinner (below right).

The only problem with the multiple horizontal bullet chart as created here is that the High, Mid, and Low legend entries are in the wrong order. Plotting the vertical axis in reverse order also caused these series to be entered in the legend in reverse order.

There are two ways to avoid this legend reversal. One is to reverse the order of the data in the worksheet, the other is to convert from a clustered bar chart to a stacked bar chart.

Multiple Horizontal Bullet, Reverse Data Order

If you have control over the data sheet, the easiest way to keep the legend entries in the right order is to change the order of the bullets in the sheet. In the range below left the bullets are listed Gamma, Beta, Alpha. When you select the bar chart data (shaded blue) and insert a clustered bar chart, the categories are ordered top down in the desired Alpha-Beta-Gamma order, and the legend is also shown in the desired Low-Mid-High order.

Following the rest of the protocol above, but omitting the Categories in Reverse Order and Horizontal Axis Crosses at Maximum Category settings, results in the following bullet charts, with and without a legend.

Multiple Horizontal Bullet, Stacked Bars

If you can’t change the order of the bullet categories, you need to insert a couple rows for calculations. In the range below, the second through fourth rows contain High, Mid, and Low values. The fifth row, labeled “Mid”, has a formula that subtracts the Low value from the Mid value, while the sixth row, labeled “High”, has a formula that subtracts the Mid value from the High value. These calculated values will be stacked together onto the Low value to display the bands in the chart.

Select the first blue shaded row in the data range (top left, below), then hold Ctrl and select the other three blue shaded rows, so that both areas are highlighted, and insert a stacked horizontal bar chart (top right, below). Format the vertical axis to plot categories in reverse order, with the horizontal axis crossing at the maximum category (bottom right below).

Select the cells containing the Actual label and values, then hold Ctrl and select the cells containing the Y label and values, so both areas are highlighted, and copy (Ctrl+C). Select the chart, and use Paste Special from the Home ribbon tab to paste the data as a new chart series, using values in rows, series names in first column, and categories in first row.

The result is the bars stacked on the right edge of the existing bars, below left. Change the series name from Y to Actual (below right). Right click the Actual series, choose Change Series Chart Type, and select the XY Scatter option with markers and no line, and format it as a marker with dark blue border and fill (bottom middle).

Select the cells containing the Target label and values and the Y label and values, and copy (Ctrl+C). Select the chart, and use Paste Special to paste the copied data as a new chart series, using values in rows, series names in first column, and categories in first row. This is pasted as another XY Scatter series, again with the name Y. Format the series using the dash marker with a 1.5 point red line and red fill (below left). Change the series name to Target (below right).

Format the secondary vertical axis (right edge of the chart) so the minimum and maximum are manually fixed at 0 and the number of categories (3 for Alpha, Beta, and Gamma, below right). Format the horizontal axis so the tick label spacing is more frequent (bottom middle).

Select the Actual series and add error bars. It’s an XY scatter chart series, so you get horizontal and vertical error bars (top left chart below). Delete the vertical error bars. Format the horizontal error bars to show the minus direction only with a percentage of 100, without end caps, using the same dark blue for the line, and make the line thick (such as 6 points in the top right chart below).

Select the Target series and add error bars. As an XY scatter chart series, it gets horizontal and vertical error bars (bottom left chart below). Delete the horizontal error bars, and format the horizontal error bars to show both directions, at a value of 0.3, without end caps, with a 1.5-point red line (bottom right chart below).

Hide the Actual and Target markers from the chart but keep them in the legend. Click on the marker for Actual to select the whole series. Then click on the marker again or click the right keyboard arrow key, which selects just the single point, and press Ctrl+1 to format the point. In the Format Data Point dialog, choose No Marker for Marker Style, which hides the Actual marker for the data point in the chart, but leaves the marker in the Actual legend entry.

Repeat the single click plus single click technique to select the marker for Target. In the Format Data Point dialog, again choose No Marker for Marker Style, to hide the marker for the data point in the chart, but leave the marker in the legend.

The result is the left chart below. If you don’t need a legend, you could have skipped all this finagling with markers, not shown a legend, and made the bullet chart thinner (below right).

Peltier Tech Chart Utility

Structured Referencing to Identify Parts of Excel Tables

Guest post by Zack Barresse & Kevin Jones
Data Automation Professionals, LLC

Zack and Kevin are VBA ninjas who have been helping people around the internet for several years. They’ve combined resources and started a company, Data Automation Professionals, which helps Excel users automate simple to complex tasks, consults on projects, and teaches the world VBA. Zack has been hanging around forums like Mr Excel and VBA Express for several years, and maintains the blog at exceltables.com. Kevin is an engineer who has been spotted around the net using the online moniker ‘zorvek’. Together they’ve written a book on Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables.

With the introduction of Tables in Excel 2007 (Tables are a re-invention of Lists, introduced in Excel 2003), we were also provided a new syntax for referencing Tables and the parts within those Tables. This new syntax is called structured referencing. The reason a new referencing method is required is because Tables are very dynamic, and the traditional cell referencing syntax would not allow robust referencing without clever use of functions as Tables as data is added and removed. As you will see in this article, structured referencing is a very powerful tool that makes your formulas dynamic while maintaining significant simplicity. If you’re not familiar with Tables, a good starting point is this blog post (with video) by Excel MVP Jon Acampora.

Tables play an integral part of modern Excel. They’re very organized, controlled, and most importantly, they have rules. These allow for a lot of built-in functionality previously unavailable. Functionality such as good data structuring, dynamic chart ranges, dynamic PivotTable sources, etc. Additionally there is some default behavior which can be nice such as banded rows and columns, cell formatting that is added to every new row of a table, and new rows auto-populating with formulas.

All examples in this article are for Excel 2010 and later. There is a slight difference between using structured referencing in Excel 2010 and Excel 2007—Excel 2007 is not covered in this article. For the purposes of discussion, the traditional method of referencing cells (i.e. A1, A2, B1:B100, A2:D100, etc.) is referred to as standard referencing.

Structured Referencing

Before we get too in-depth here, let’s make sure we have a good understanding of what is meant by structured referencing. Structured referencing makes it easier and more intuitive to work with cell references in Tables. It allows you to reference a Table’s parts such as the columns, header rows, and total rows without using standard referencing (R1C1 or A1 syntax) but rather by using the Table’s name and other constants such as column header values which makes references easier to read because recognizable names are used. This eliminates the need to use complex formulas or rewrite formulas when the Table structure is changed or data is added or removed. Formula audits are also made much easier.

Let’s look at a quick example of referencing the hours, let’s say this is stored in column A, and the rate, stored in column B. The standard referencing formula to calculate the total billable amount in row 2 is:

=A2*B2

The structured referencing formula in row 2 (and in all other rows) is:

=[@Hours]*[@Rate]

As you can see, with structured referencing it is much clearer what the formula is doing than with standard referencing. With standard referencing, we have to navigate to the source to determine what, exactly, is in cell A2 and B2. With structured referencing, we have greater transparency with makes development, maintenance, and auditing easier. In today’s world of Excel, with groups like EuSpRIG and heightened auditing and maintenance requirements, transparency in spreadsheets is as important as it’s ever been, and structured referencing goes a long ways to assist.

In the examples below, we will look at a simple Table which has three columns and six rows, with both the header and total rows showing. The image below is used for the next examples.

Starting Point for Table Formula Examples

Before we get too in-depth on how the referencing actually takes place, there are a few rules to structured referencing which must be identified. In any single structured reference you may have a

  • Table name,
  • special identifier, or
  • column name.

Generally only the column name is required, but every structured reference will have some combination of these three elements. Below are the basic rules of when to use these elements.

  • The Table name used if
    • more than one column of a Table is being referenced, or
    • the column is being referenced from outside of the Table.
  • A special identifier is used if a specific part of the Table is being referenced, i.e. the total row.

A key part of structured referencing is the use of square brackets. Square brackets are used to identify a reference as a structured reference versus a standard reference. Every structured reference (except the Table name itself) is enclosed in a set of square brackets. There are two occasions where you will have an additional set of square brackets.

Single-column cell reference

Column name of “Column” (no quotes used as the cell value) has a reference of:

[@Column]

Column name of “A Column” (no quotes used as the cell value—note the space) has a reference of:

[@[A Column]]

Multi-column cell reference

TableName[@[Column1]:[Column2]]

In a multiple column reference, Excel will automatically place a separate set of square brackets around each column name regardless of whether or not there is a space or other special character in the name, as well as append the Table name to the reference. This is to identify it as an individual column within a multiple column reference.

Additionally, below are the characters which Excel identifies and automatically puts an additional set of square brackets around.

  • Space ( )
  • Line feed
  • Carriage return
  • Comma ( , )
  • Colon ( : )
  • Period ( . )
  • Left bracket ( [ )
  • Right bracket ( ] )
  • Pound sign ( # )
  • Single quotation mark (apostrophe) ( ‘ )
  • Quotation mark (  )
  • Left curly bracket (brace) ( { )
  • Right curly bracket (brace) ( } )
  • Dollar sign ( $ )
  • Caret ( ^ )
  • Ampersand ( & )
  • Asterisk ( * )
  • Plus sign ( + )
  • Minus sign ( – )
  • Equal sign ( = )
  • Greater than ( > )
  • Less than ( < )
  • Division ( / )

This Row

The ampersand character (@) is used to identify “This Row” in a structured reference. This is also known as the implicit intersection of the row in which the reference resides.

Special Characters

Excel uses special characters to qualify structured references (discussed later in more detail) and, when these characters are included as part of a column name, they need to be “escaped” so that Excel does not interpret it as a special reference qualifier. The apostrophe ( ‘ ) is used for this escaping.

Below is a list of all special characters used to qualify structured references and must be preceded by an apostrophe when part of a column header.

  • Left bracket ( [ )
  • Right bracket ( ] )
  • Pound sign ( # )
  • Single quotation mark (apostrophe) ( ‘ )
  • At sign ( @ )

Special Identifiers

When attempting to reference specific parts of a Table, you will need to use a special identifier. There are only five. Let’s take a look at each of them and a picture for visual reference. In the following examples the referenced area is outlined with a red box.

[#All]

Table Special Identifier [#All]

[#Headers]

Table Special Identifier [#Headers]

[#Data]

Table Special Identifier [#Data]

[#Totals]

Table Special Identifier [#Totals]

@ (or [#This Row] in Excel 2007)

Table Special Identifier @ or [#This Row]

Note that when referencing Table parts which are not visible or enabled such as when a header or total row isn’t showing, the reference will evaluate to a reference error (#REF!). Let’s look at an example formula:

=TableName[[#Headers],[Column 3]]

The above formula references the Table named “TableName”, with the special identifier of the header of “Column 3”. This is a single-cell reference. If the header row is showing the result will be the column name or, in this case, “Column 3”. If the header row is not showing the result will be “#REF!”.

This means we can use formulas to tell if a header or total row is visible or not.

Header row visible formula:

=IF(ISERR(TableName[[#Headers],[Column 3]]),"No","Yes")

Total row visible formula:

=IF(ISERR(TableName[[#Totals],[Column 3]]),"No","Yes")

Another Table part which can possibly not exist is the body. It’s important to note this will not affect formula evaluation, but does have a serious impact in VBA which is covered later in this article.

In Data Validation

Using Tables as a source for an in-cell drop down can simplify your spreadsheets, but Table ranges cannot be referenced directly in the data validation source formula. Instead you must name the range and use that name in the validation formula. To do this, navigate to the FORMULAS tab and click ‘Name Manager’, then click ‘New’.

Since the name of any Table must be unique  for the entire workbook, you cannot name a range the same as a Table name. This is why some people either use Hungarian Notation with naming Tables, or preface all Table names with a “t”. For example, if you have a Table with a list of countries, instead of naming the Table “Countries”, you would name it “tCountries”. This way if you wanted to have those countries as a data validation drop-down list, you could name a range of “Countries” which points to the Table range. Here is an example:

Name: “Countries”

Refers to: “=tCountries[Countries]”

In Charts

Putting your data into Tables and using structured referencing makes it easier to create dynamic charts that change as the data in the Table changes. As to not reinvent the wheel, we’ll refer you to Jon Peltier’ great post about this topic Easy Dynamic Charts Using Lists or Tables. The takeaway from the blog post is Tables make great data sources for charts which grow and shrink as the source data set changes. It’s a nice alternative from having to manually create dynamic named ranges.

In Formulas

Putting all of this information into practice can be confusing. Let’s look at some specific structured referencing syntax examples in formulas. Keep in mind that structured references always evaluate to a range of cells and are treated like any other range reference in Excel, so if you’re referencing more than a single cell it must be used as an aggregate, range array, or lookup array, depending on the formula to which they are being passed.

There are five specific locations we can reference:

  • Body column
  • Entire column
  • Column header
  • Column total
  • Column cell

The examples below assume the Table’s name is “TableName”, using column “Column Name” where applicable.

Specific Column Body or Data Excluding Header and Total Rows

TableName[Column Name]

Specific Column Including Header and Total Rows

TableName[[#All],[Column Name]]

All Data Excluding Header and Total Rows (Data Body)

TableName

All Data Including Header and Total Rows

TableName[#All]

Row Across All Columns

TableName[@]

Row for One Column (Single Cell)

TableName[@[Column Name]]

Header Row Across All Columns

TableName[#Headers]

Header Row for One Column

TableName[[#Headers],[Column Name]]

Total Row Across All Columns

TableName[#Totals]

Total Row for One Column

TableName[[#Totals],[Column Name]]

Let’s assume we have a column in our Table which is titled “Column 3”. Below are examples of the types of structured references we can use to reference specific parts of that column.

Specific Column Body or Column Data Excluding Header and Total Rows

TableName[Column 3]

Referencing Table Column Excluding Header and Total Rows

Specific Column Including Header and Total Rows

TableName[[#All],[Column 3]]

Referencing Table Column Including Header and Total Rows

Header Row for One Column

TableName[[#Headers],[Column 3]]

Referencing Header Row for One Column

Total Row for One Column

TableName[[#Totals],[Column 3]]

Referencing Total Row for One Column

Row for One Column (Single Column Cell)

TableName[@Column 3]

Referencing One Cell

In VBA

There are two basic methods for referencing Tables and parts of Tables from VBA. The easiest is to use the same syntax as described above for formulas where you pass the Table information as a text string to the Range object. The other method is to use the Excel object models ListObject and its child properties and methods. Both are described below.

Using Range and Evaluate

Just as with standard referencing, the Range object evaluates structured references passed as a text string. For example, to reference the body or data portion of “Column 3” in the Table named “TableName” using the Range method:

Range("TableName[Column 3]")

Note that no qualifying worksheet is required because the table name has to be unique across all worksheets. This is the referencing style you will see when using the Macro Recorder.

Using the Object Model’s ListObject

To reference any part of a Table using the Excel object model, we have to first identify the Table object itself. The ListObject object is how Excel exposes a Table in the Excel object model. It is contained in the collection ListObjects which  is a child of the Worksheet object. Use this syntax to reference a specific Table on a worksheet:

ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")

ListObjects, being a collection of list objects or Tables, can also be accessed with an index number:

ThisWorkbook.Worksheets("Sheet1").ListObjects(1)

The index number of the Table is the order in which it was created on the worksheet and is a read-only property.

Once we have the Table’s ListObject object we can access and manipulate any part of that Table. The more commonly used properties and methods are discussed below. Each example starts with this code:

Dim Table1 As ListObject
Set Table1 = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")

Range Property

The Range property returns the entire Table as a range object including the header and total rows.

Dim Table1Range As Range
Set Table1Range = Table1.Range

HeaderRowRange Property

The HeaderRowRange returns the Table’s header row as a Range object. The range is always a single row – the header row – and extends over all Table columns. When the header row is not showing this property returns Nothing.

Dim Table1HeaderRowRange As Range
Set Table1HeaderRowRange = Table1.HeaderRowRange

DataBodyRange Property

The DataBodyRange returns the Table’s body as a Range object. The range is every row between the header and the total row and extends over all Table columns.

Dim Table1DataBodyRange As Range
Set Table1DataBodyRange = Table1.DataBodyRange

When the Table does not contain any rows the DataBodyRange property returns Nothing. This may be confusing when looking at the worksheet as it will appear as if a single row exists. This is the only case when the property InsertRowRange returns a range which can be used to insert a new row. Effectively InsertRowRange and DataBodyRange are equivalent.

TotalRowRange Property

The TotalRowRange returns the Table’s total row as a Range object. The range is always a single row – the total row – and extends over all Table columns. When the total row is disabled this property returns Nothing.

Dim Table1TotalRowRange As Range
Set Table1TotalRowRange = Table1.TotalRowRange

InsertRowRange Property

The InsertRowRange returns the Table’s current insertion row as a Range object only when the Table DataBodyRange object is Nothing (the Table has no data rows); it’s Nothing when the DataBodyRange is a Range (the Table has one or more data rows). While this range was always the last data row in Excel 2003 (the row with the asterisk), it was partially depreciated in Excel 2007 and remains so in 2013. In Excel 2007 and later versions it only returns the first data row and only when the Table does not contain any data. Otherwise it returns Nothing.

Two additional properties or collections provide access to the rows and columns in the Table. Each collection provides access to all of the ListRow and ListColumn objects in the Table. Each ListRow and each ListColumn object has properties and methods.

ListRows Property

The ListRows property returns a collection of the rows in the Table’s DataBodyRange as a ListRows object type. It behaves very much like a Collection object and contains a collection of all the ListRow objects. Rows are referenced only by a one-based index number relative to the first row. An empty Table has no rows. The ListRows.Add method can be used to insert new rows.

Debug.Print "The Table has " & Table1.ListRows.Count & " rows."

ListColumns Property

The ListColumns property returns a collection of the columns in the Table as a ListColumns object. It behaves very much like a Collection object and contains a collection of all the ListColumn objects. Columns are referenced by a one-based index number relative to the first column or the column header. The ListColumns.Add method can be used to insert new columns.

Debug.Print "The Table has " & Table1.ListColumns.Count & " columns."

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

Peltier Tech Chart Utility

 

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