Share via

Scatter plot arranged by category?

Anonymous
2011-08-12T17:23:11+00:00

Hello,

I'm looking for a straightforward way to draw a scatter plot to show numerical results, grouped by category label. x values will be the numerical value, and y values will be the the categories.

I have data that looks like this

Data table

Label Value

USA     2

USA     4

USA     5

USA     6

USA     8

USA     9

USA     12

USA     23

Canada   2

Canada   3

Canada   5

Canada   9

Canada   10

Canada   11

Canada   14

England 21

...

Egypt   20

And I want a chart that will look like this:

Chart

USA   | x  x xx x x   x          x

Canada | xx x    x xx   x

England | x     xx         x  x

France |   x  xxx x   xx

Egypt |  x   xx   x       x

+--------------------------------

Result  1 5 10 15 20 25

When I've tried this before, the chart just prints every instance of the label in my data table, and fails to group them together. This seems like a fairly common type of graphic, so I'm hoping someone has a good idea here.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2011-08-15T19:10:51+00:00

The '2' and '3' can be restored by clicking on

Field Settings > Show Items with no data.

With non-integer data, the categories can be

reduced to a desired number of equal bins by Grouping.

Both options have been incorporated in the updated file.

Please re-load file.

Was this answer helpful?

0 comments

11 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-08-15T13:51:25+00:00

    No, I'm looking for a real chart, with the points plotted on an axis. I'm not looking to summarize any data in a histogram, I would like to see each individual result on the chart.

    Thanks, though.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-08-13T09:23:16+00:00

    If I understand correctly, you need no true chart, but virtually a table that would, in a way, represent histogram. If you simply categorize the counts, then your table would look like

    USA            2    4     1         1

    Canada       1    2     1    1

    England                              1

    Egypt                            1    

    For yet better graphical image, you can create another table, where simply instead of counts from original table you will apply the function

    = REPT(“×”, count)

    in the corresponding cells with a suitably chosen letter.

    Regards

    Petr Bezucha

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-08-12T19:39:18+00:00

    Andrea,

    That is a good idea for a workaround, but I don't think it will work for me. It would need to be custom labeled each time I generate the chart - this is something I intend to generate several times on a repeating basis. Additionally, I'm going to have as many as 60 categories.

    Any other ideas?

    Thanks,

    Nathan

    Was this answer helpful?

    0 comments
  4. Anonymous
    2011-08-12T19:22:26+00:00

    One way you could cheat is to represent each of your countries with a number (e.g. USA is 1, Canada is 2, etc) and plot an xy scatter graph.  You could then overlay text boxes on the y-axis to obscure the numbers and enter your countries (set the fill colour of the text boxes to white so you can't see the axis values through them).

    Was this answer helpful?

    0 comments