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

 

[wsjackman]

how are you entering the data into the chart. I have not had the problem you are talking about

 

[dbiggers]

WS,
The chartable XY data is extracted from two column in a larger worksheet/table based on the user's criteria. I use then INDEX function with a row parameter to set the data in the chart's Data Values range. The user changes criteria and the Data Values content changes to different rows from the master table. The Chart construct is static. Does that clarify it? Thanks.
Dave

 

[wsjackman]

Is this approximately what you are having happen?
you have two data sets
xy input (A1:B10)

A1:B10 are index match functions based on D1:E100
​

master data set (D1:E100)
you set up an XY graph based on A1:B10
you change the index functions for A1:B10 and the chart changes to a line graph

 

[dbiggers]

WS,
I think you have it. A1:B10 contain the XY values to be charted. The user, through other excel magic, sets a flag (in C1:C10) which contains index numbers to the base data in Base sheet so that INDEX('Base'!$D$1:$D$100,C1) populates the X value in A1 and INDEX('Base'!$E$1:$E$100,C1) populates the Y value in B1, and so on for as many XY pairs (rows) as the user wants charted. In this example, there could be from one to 10 XY's to be charted depending on the usere's criteria.

 

[dbiggers]

WS,
Further, using this example, when I first build the chart using the wizard and assigning the A1:B10 to a single data series, the X values are charted and used to determine the X axis. Life is good! But when I reset the flags so a different set of XY pairs are stored in the Data Values range, the charted X values change to the relative position (now number) within the Data Values set (... 1 to n), totally ignoring the stored X value, and the X axis range becomes 0 to ~7 (e.g. the count of XY pairs to be charted.) Then, when I reset the flag so that the original XY pairs are repositioned in A1:B10, the X axis and charted X values revert back to those present in the Data Values range.

Does that add some clarity to the picture?
Dave

 

[wsjackman]

when you get down to the 7 pairs with the second filter for C

what shows in A8:B10?

 

[wsjackman]

actually i should have also asked what shows in C8:10?

change your A formula to something like
=if(c1="",NA(),your index formula)

if you do not like the NA() visble in the spread sheet, you can use conditonal formating to blank the cell out.

you probably have a formula which shows a "" when there is no C value
the "" is non numeric and will cause the x axis to become a catagory axis.

 

[dbiggers]

WS,
The entire formula in the X and Y cells is ...
=If(C3<>"",INDEX('Base'!$D$1:$D$100,C3),"")
A quick test replacing the "" with NA() ...
=If(C3<>"",INDEX('Base'!$D$1:$D$100,C3),NA())
seems to have fixed that issue. The X values now get used for charting. Problem No. 1 solved. NA() is not the same as "". THANKS! ...

Now to issue #2 ... how can I get each point to display its label when I move my cursor over the point? Continuing with this example, each XY pair also has a label on the same row ... e.g column A contains the label, B the X value and C the Y value. Rather than the Series name with the coordinates, I'd want the point label. Is that doable with a single series approach or do I have to make each XY pair a separate series? ... which, unfortunately, I suspect is the case.

Then, if that is true, is here any way to globally format all data point in all series to use the same color and same point marker?

Thanks for your help, thus far ... and sorry if this seems like asking for an inch but taking a mile

 

[wsjackman]

take one graph with the max number of points you expect to have.

you can either format each point separately or record a macro changing one or two points and modifying the macro to loop through all the points.

after you have the graph the way you want it, add it to the user defined charts in chart type

just select this type of chart when you next make it.