XY Scatter Chart with real X values

[dbiggers]

I have 200+ XY pairs I want to chart and see a visual representation of their distribution. Then I want to cursor over exceptional points and see their identity. I don't need (nor want) data labels, disparate markers, nor a legend. I just want the points scattered on the xy plane. Also, the data set changes depending on user-controllable filter criteria.

There are two problems:
1. If I build it through the wizard as a single data series, everything is fine except that the charting process seems to replace the X value of the XY pair with a sequence number once I swap in a new data set, and ...
2. I seem to loose the individual point identities for cursor-over purposes.

The only solution I can think of for these issues is making each XY pair a one-point Series, but that's a hugely laborious way to do it. If that's it though, is there a way to globally load the independent data series and set every data point marker and color to be the same?

However, if I could do it as a single series, then I still have the issues above. Is there a way to prevent Excel from converting the X values to sequence numbers once a new data set is swapped in, and is there a way to identify the individual points for cursor-over?

Any thoughts? Thanks ...

 

[dbiggers]

WS,
I don't see any way to format each point to "read" the A column for it's label, so I assume you mean that each point is an independent series which needs to be loaded and formatted once so that a template can be built. There is no way I can describe (in this case) 230 rows spanning columns A, B & C where A is a label, B is the X and C is the Y? I have to enter each row (data label + data point) individually, then save the whole thing as the template. ... true?

Thanks for all of your help, and sorry if I've chewed up part of your weekend.
Dave

 

[wsjackman]

maybe
select a chart and use a macro like

(I had added a couple of series in the recording of the macro) so the macro starts with the fourth series)

many experienced macro users will shudder on the fragility of this macro.
I normally make macros for one time brute force use only so do not make them "nice".

Thinking about it more, the user defined chart is not really the answer, since the hardest part is getting the multiple single pair series.

you might want to work on the macro to make it better for multiple uses.

Sub Macro1()
'
' Macro1 Macro
'
'
For r = 4 To 230
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(4).Name = "='Sheet1'!$A$" & r
    ActiveChart.SeriesCollection(4).XValues = "='Sheet1'!$B$" & r
    ActiveChart.SeriesCollection(4).Values = "='Sheet1'!$C$&r"
Next r
End Sub

 

[dbiggers]

WS,
You're probably right with respect to macros. I'm in the info provisioning (vs. using) side of the process so anything I build and distribute has an implied support role after the fact, and when I distribute something built around macros, it usually comes back to haunt me. In this instance though, it's a one-use time saver which won't live on in the final distributed product.

And just for clarity, once the chart is defined to use data living in those (up to) 230 rows, it should remain pretty static. Users filtering and retrieving data from the base set and staging it in those designated "Data Values" rows is relatively straight forward and bullet proof ... e.g. no future support required

Thanks again, for all your help. I've learned several things that will help me beyond just this current dashboard app.
Dave