How to populate legend of a sccater chart with custom values

[anupgeorge]

Hi,

This is my first question in excel forum. I have a table of data which I plan to plot in a scatter plot as follows

Name Competency Performance
baron 1 1
davis 2 2
dingan 2 2
sarin 4 1

I am plotting the happiness score in X axis and personality in Y axis. I need to name each plot. The data set has close to 200 names. If I name each data point then the graph becomes too cluttered. I am planning to name each data point in the legend which means the name of each data point in the legend shall be taken from a different column i.e name. Please let me know how this can be done. I have attached a screenshot of an example

Regards,
Anup



excel.gif

[MrShorty]

As far as I know, there is no way to use "custom" values in Excel's built in Legend object. Any approach that I would suggest would center around a "add a dummy series, format it as desired, and add custom data labels to that series" kind of thing. This kind of approach is illustrated here (http://peltiertech.com/double-legend-in-a-single-chart/ ) for a column "panel" chart, but should work just fine for an XY chart like you have. Your profile indicates that you have 2013, so you may not need Rob Bovey's add-in, since this custom data label functionality was added to Excel in 2013.

[dflak]

This sort of does what you want. I have a bunch of shapes (included a zip file) and, in my case they happen to be in C:\Users\dflak\Temp\Shapes.

As a courtesy, I copied and pasted the shapes into Column D. The code goes through and assigns the shapes to the markers. I just noticed that I turned off the borders on only one of the shapes.

[anupgeorge]

@MrShorty: Thanks for the link. will check it out and update this post if it works

@dflak: Thank you so much for the effort of bringing the values into an excel.That excel is exactly the stage where I am right now. If you turn on the legend you will find that in the legend the shapes have been assigned numbers present in Column B. What I was looking for was some method where the shapes are assigned values in column A i.e the names. Example: The Plus sign is currently assigned 1 in the legend. I want it baron.

[anupgeorge]

Got it. Thank you so much @MrShorty. The link is real good. So the trick is we need to create two graphs in the same sheet. One plot will have name and competency as parameters and the other will have competency and performance as parameters. We hide the legend of the first plot and hide the plot area of the second plot. this can be done by brining in transparency of legend as 0% and putting in solid fill. Nice hack btw. Thanks a lot guys