Scatterplot with three sets of values

[Character_Zer0]

I am running into difficulties with what should be a very simple chart, not sure what I'm doing wrong! I'm basically trying to mimic a simple scatterplot like you see here: http://vixcentral.com/cefexample/

I've attached the data with three values of data needed to replicate. This orange line for "12 Mo NAV Div Yield %" should actually be the X-Axis, that is what is throwing things off, but I cannot seem to get it to work as intended. Any ideas where I'm slipping up? THANK YOU!!

[MrShorty]

As a guess, when you create a chart, Excel tries to guess what you want for X and Y series, and it often gets them wrong. I find that many times when I create a chart, I create the chart, then go in and delete the chart source data (or delete all of the data series) using the select data dialog. Then add the desired series myself. Have you tried that?

[Character_Zer0]

As a guess, when you create a chart, Excel tries to guess what you want for X and Y series, and it often gets them wrong. I find that many times when I create a chart, I create the chart, then go in and delete the chart source data (or delete all of the data series) using the select data dialog. Then add the desired series myself. Have you tried that?

Yes, I tried tinkering within "Select Data" and cannot seem to get it right

[MrShorty]

Can you describe what you have tried or put some of those failed attempts in your file? I sometimes think those failed attempts would help us understand what you are trying to do.

If I understand what you want, it seems like it should be:

Enter Select Data dialog
Remove all automatically created legend entries
Add new legend entry with column B as the y values and column C as the x values

Is that what you are trying to do?

[Character_Zer0]

Can you describe what you have tried or put some of those failed attempts in your file? I sometimes think those failed attempts would help us understand what you are trying to do.

If I understand what you want, it seems like it should be:

Enter Select Data dialog
Remove all automatically created legend entries
Add new legend entry with column B as the y values and column C as the x values

Is that what you are trying to do?

Thanks for the response. Yes, exactly, with also plotting values from column A, ticker. Here's an example of what I'm trying to mimic: http://vixcentral.com/cefexample/

So "12 Mo NAV Div Yield %" on the x-axis, and "Prem/Disc %" on the y-axis as you said. The problem I am running into is that column C, the "12 Mo NAV Div Yield %" is not showing on the x-axis, and it's almost like it's showing a count of the values in a straight line over the 0 row rather than plotting the actual values. This is where I am stuck. See the orange "line" on the chart below? That should be plotted as the x-axis, and it should show the true values. For some reason it's showing values ranging from 0 to 92.34 all as 0's across.

\1

[Character_Zer0]

So I am 90% of the way there, thanks so far! I was running into problems because I was trying to add ticker, column A into the equation. Now that I am only using columns B and C, it is working.

However, I now want to add ticker so that when you hover over the appropriate bubble, it says which ticker it is. Even better, I would love to be able to display the ticker so you don't have to hover over, but I am having trouble doing so. Any suggestions? Thanks again for the help!

[MrShorty]

Even better, I would love to be able to display the ticker so you don't have to hover over, but I am having trouble doing so.

If I understand what you are trying to do, this feature was added natively to Excel in 2013, but was readily available to previous version. The easiest way is to use RobBovey's free XY chart labeler add in (appspro.com), which will readily allow you to add the ticker text as a data label for each point.

[Character_Zer0]

That did it, you're the best! I'm not sure why Excel doesn't have this built in, very strange