Excellent. This is the sort of thing I’d first think of using pivot tables for, only to then realise that pivot tables will only give me aggregates, not the individual data points.

Although I *can* do it with Pivot tables if I have to, using COUNTIF(C$3:C4,C4) to establish a unique serial number, and “Max of X” etc. for the summary values. It’s all a lot of work though.

Wow! I’ve looked all over for a way to label data points, and this is precisely the solution.

I previously tried a couple of labeling work-arounds, but they only add labels to data points in a single series. Ergo, they’re not robust if you make changes to the input data range. For example, if you delete one input row, the remaining data points are mis-labeled.

Jon’s solution takes the correct approach by creating multiple series, and then simply displaying the series name.

(Jon - please feel free to edit/rephrase this comment). Thanx.

Bruce -

If you only need data labels on identically formatted points, there are other approaches. Excel does not natively support data labels from worksheet ranges, but you can individually modify labels and even link an individual label to a worksheet cell. Two free utilities that handle this for entire series of points are:

Rob Bovey’s Chart Labeler (http://appspro.com)
John Walkenbach’s Chart Tools (http://j-walk.com)

I’ve recently thought that, fine as the Bovey and Walkenbach solutions are, a new tool needs to be written for more convenient labeling. It’s especially annoying to have to go through several click-and-drag stages again when I’m developing a chart and have changed e.g. the number of rows. I’d like an add-in with a “refresh” button to say “I’ve made some changes, but not fundamental ones, please re-apply the labels as before”. I’d also like a “apply this rectangular range to several series” instead of going through the menus for each series to select a single vector range each time.

Especially as MS has once again not incorporated this basic bit of fuinctionality into their latest version of charts :-(

Derek -

Hey, I’m in the choir you’re preaching to. The problem with the available programmatic solutions is that the individual labels are linked to individual cells (not a series of labels to a range of cells). If points are inserted or deleted (or hidden, as by a filter), the label linkage is not updated, and the wrong label now appears by each point.

On a case-by-case basis I’ve applied VBA-based solutions that either update the labels when the data has changed, or formula-based solutions that use various index or lookup functions to extract data from its range and place it into a ‘conditioned’ data range which the chart uses. These cannot easily be generalized.

Oh, I just remembered the other feature I wanted from Labeler 2.0: select a vector range (i.e. one column wide or one row high) and ask for that to be applied to the nth point of each series. This would usually be the first or last, for curve labeling.

In practice, you can use the “select a rectangle” and let all but the last etc. be blank, but the special version would be elegant.

Derek -

This simple utility labels the last point of each series with the series name:

http://peltiertech.com/Excel/Charts/LabelLastPoint.html

Nice. I’d make a few mods: 1) If the user selects a single cell, use the current region; 2) Sort the range (unless someone believes it will always be sorted beforehand); 3) Remove legend and gridlines if they exist (if the activechart is empty to begin with, these elements will be inserted when you create the chart); 4) Set the chart type to xlXYScatter (scatter without lines), since if the chart is empty, it’ll insert a scatter with lines.

I forget how I stumbled across this, but it is possible to data labels from a range to a chart, without programming.

http://support.microsoft.com/default.aspx?scid=kb;en-us;214040

Oddly enough, it uses Lotus 1-2-3 commands to do something that Excel cannot natively do.

Colin - There’s always room for improvements and error prevention. Regarding items 3 and 4, if the original chart meets these conditions, so will the adjusted chart.

Jason -

Interesting find. It seems to have been lost by the time Excel 2003 was released. I don’t have Excel 2000 on this computer: What happens if you create a chart using this technique, then delete a row from the middle of the data range? Do the labels adjust properly?

Jon, agreed. The beauty of code snippets is that they provide a good starting point for new functionality and can be readily modified to meet specific needs if required. (1) and (2) came to mind immediately while reading the post and (3) was based on my starting point, which was an empty chart. As for (4), even after the chart is set as a scatter without lines, every time the macro runs, in Excel 2007 it reverts to a scatter with lines.

Colin - If the XY Markers Only chart contains data before the procedure is run, it will be an XY Markers Only chart after the procedure is run. It’s simple enough to adjust. In the With srs block, just before End With, insert this line:

.ChartType = xlXYScatter

Jon, I’m missing something here. Why add the line if the chart is going to remain XY Markers only *after* the procedure is run? I added that line in the first place (point (4) of previous post) because every time I ran the macro, it added the lines to the chart, even if I manually changed the chart to Markers only between runs. The point I’m making is that in Excel 2007, this is what I see happening - the markers only chart isn’t sticking when the macro is run. At any rate, the discussion here is somewhat moot because the line you mentioned already solved the problem.

Colin - I was adding the line in response to your problem, which apparently occurs in Excel 2007 only. I have to admit that I didn’t test this in Excel 2007, only 2003. I don’t use Excel 2007 for any of my own important work, only for client work if needed and to check out bugs and irregular behavior.

Jon…yes, I just checked and the original macro works fine in Excel 2003.

Jon - I used the Lotus 1-2-3 trick successfully in Excel 2002. We’ve since upgraded to Excel 2003. The chart I made still shows the data labels correctly, but if I delete a row as you suggested, it does not update the labels accordingly.

Jason - Excel 2002 has ‘Help for 1-2-3 Users’ on the Help menu then? In Excel 2003 I could find no mention of this either in Tools > Options or in Tools > Customize.

Dear Sir,

Following your article from few years back about Gantt charts I wrote a VBA procedure (Procedure1) to make a Gantt chart. Everything works fine when I input series values as Ranges.
However, when I try - in Procedure2 - to change Ranges for Arrays in series input, I receive error 1004 for .MinimumScale and .MaximumScale properties, which says that these properties can’t be set. Why is it so?

Best regards,
Piotr Pikielny

Hundreds of lines of code deleted…

Pyotr -

That was too much code to wade through without a little more guidance. The .MinimumScale and .MaximumScale properties are used at least twice in the procedure, so I don’t know where the error occurs. Also, 1004 is a very common error number, and it has a variety of corresponding error descriptions, so I don’t know the reason for the error. Step through the code and make sure the values being assigned to the scale properties are appropriate. Also make sure that when you set the maximum, it isn’t smaller than the existing minimum, and when you set the minimum, it isn’t larger than the existing maximum.

[...] He does that by creating various named ranges and manually adjusting the color based on the range. Read more on how to this. Download and play with his [...]