X/Y Scatter plot

Hi all!

I have gathered data through a survey which has the following layout:

NameA | Value 1 | Value 2
NameB | Value 1 | Value 2
NameC | Value 1 | Value 2
NameD | Value 1 | Value 2
...
..
.

All values are between 1 and 5. I would now like to visualize the results using a scatter plot, X axis values 1-5 and Y axis values 1-5.

E.g.: NameA scored 3 in value 1 and 5 in value 2, so there is one datapoint on the scatterplot on 3/5.

For some reason I couldn't figure out how to do that in Excel. Excel keeps auto-summarizing my data on the X axis, but all I want is both scales to be 1-5. I appreciate every input!


Thanks,
David

Can you post an example workbook showing what you've got? There shouldn't be an issue plotting that as an XY scatter, though Excel is usually better at guessing if you leave the header cell for the first column blank.

I don't understand what is going on. What do you mean by "autosummarize" data on the x-axis.

If I were creating a scatter chart, I would expect to:

1) Select the "value 1" column and the "value 2" column.
2) Insert chart -> XY scatter -> desired subtype
3) If Excel gets something wrong (such as giving me two data series plotted against "point number"), bring up the Select Data dialog and fixing Excel's mistakes.
4) If the X values are still "point number" instead of the actual Value 1 values, then I would expect to find a text string (including a number stored as text) somewhere in the value 1 range. Test using the ISTEXT() function and fix by doing whatever I need with the text strings in that range.

Does that help? If not, can you upload a sample sheet (with dummy data, if needed) that illustrates your problem?

You'll need to upload a workbook, not a picture.

Did you read through my whole post? It looks to me like you are up to step 4 -- you have text strings in your X values range. The strongest evidence for this is that the 6th data point (Name F) is plotted at x=6 and y=0, even though the picture suggests that point F is "blank". This suggests to me that point F is not truly blank, but that these cells contain some kind of non-printing character (empty string "", or space, or something similar).

I think the best thing you could do right now is to read this: https://peltiertech.com/mind-the-gap...g-empty-cells/

Onedit: after looking at your file, I see that B7 contains a 0 length text string, which is causing Excel's scatter chart to ignore the X values column, and plot some of Y values as 0. You need to do something different with those "empty - but not really empty" cells.

Your issue is as MrShorty says. (You can tell because Ctrl+Down arrow/Up arrow will not stop at any of the "blank" cells)

Not easily. What I usually do is to use a semi-transparent fill, so that when you have more points overlapping, they get darker.

Now what you are describing sounds like a bubble chart to me. Where you need to have a summary table that will show the existing A & B combinations and count how many of each combination, then use that table to create a bubble chart. A pivot table might be a convenient way to create that summary table. If you use a pivot table for this, you need to know that a pivot chart cannot be a bubble chart, so you will need to employ some "trickery" to get a bubble chart built off of a pivot table (https://peltiertech.com/regular-char...-pivot-tables/ ).

Do you need help building the summary table, or do you already know how to do that?