Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a graph that was generated from a data set that contains:

(date, value, annotation) 

The annotation is a constant value [its either there or is blank] and I would like to add in the third bit of data into the graph I have. An example of this is in the image. The blue line is a graph of the (date, value) graph, and I would like to add in the red dots as graphing (date, annotation@value).

Is there an easy way to do this in excel, without having to modify the appearance of the data?

enter image description here

share|improve this question

3 Answers 3

up vote 2 down vote accepted

I might tackle it by first plotting the 'value' series and include data labels for ALL the points. Next, plot the red series as a 2nd 'value2' series that is a subset of the 'value' series. Then, record and modify a small macro similar to this one, which replaces all the 'value' labels with 'annotation' labels. Here's my small table and chart, and the macro. (I named the chart as 'myChart'.) Hope this makes sense!

Sub CustomLabels()

    Dim i, myCount
    ActiveSheet.ChartObjects("myChart").Activate
    myCount = ActiveChart.SeriesCollection(1).Points.Count

    For i = 1 To myCount
        ActiveChart.SeriesCollection(1).Points(i).DataLabel.Text = Range("C" & i + 1).Value
    Next i


End Sub

enter image description here

share|improve this answer

You could follow this protocol:

Change the primary X axis to a Text type. Plot the second series (the annotations) on the secondary axis. Make sure the annotation series uses column C as its category labels. Delete any secondary axes Excel adds for you (the series will still be treated as if they are on separate axes, but will both use the primary). Add labels to the annotation series using the category labels option.

share|improve this answer
    
Thats an interesting suggestion... all though the data is more helpful being on the plot its self. –  monksy Feb 21 '11 at 5:20

I don't think there's a built-in way to do what you want as the data label is limited to the series name the X-axis value or the Y-axis value, not another arbitrary column.

There's a sourceforge project addin that looks like it could help though: http://xltoolbox.sourceforge.net/annotate.html

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.