Quick scatter chart with multiple series names

As with a lot of charting questions, I think that this ends up being more about data layout. If you arrange the data correctly, creating the chart should be easy.

Something like this:

Please Login or Register  to view this content.

With your data arranged like this, you should be able to select this block, insert scatter chart, and the chart should just about create itself. If Excel gets rows/columns wrong, you may need to click on "switch row/column" or go into the "select data dialog" and manually select the first row as the horizontal axis data.

It doesn't seem to me that the formulas should be that complex. With a copy of your ID#s down column A starting in row 6 immediately below the input data as you show it, something like =IF($A6=B$1,B$5,NA()) copied down and across the desired block should quickly and easily create the data table I suggest. Note how I used a mix of relative and absolute references to make the formula easy to copy. Also note that I am using the N/A error to simulate "blank" cells for the chart (see http://peltiertech.com/mind-the-gap-...g-empty-cells/ )

Other possibilities may depend on your exact requirements. You state that you want each data point to have its own series name, which means that each data point needs to be in the chart as its own data series. Without changing the spreadsheet, the only way I see to have each point as its own series is to select each data point individually then add it to the chart (using copy/paste or the select data dialog). That will be tedious and inflexible.

Does each data point really need its own "series name"? What is the purpose of having a legend entry/series name for each data point? If the question behind the question is "how to help the reader identify each point", it might be easier to use Data labels rather than series names. Rob Bovey's XY chart labeler add-in (appspro.com ) is probably the easiest way to put such data labels onto a data series. Then you could add the data as a single data series, format so that the color/style changes with each point, and each point will have a data label that identifies it for the reader.

Does any of that help further?