excel:changing the symbol and color of a marker based on grouping

Question

I have a dataset that looks like

ID    Vehicle_grp    count    mpg  
000     Car           5       10
        Motorbike    20      100
        Other         1       25
001     Car          30       60
        Motorbike    28       45
        Other        85       35
002     Car         100       10
        Motorbike    20      200
        Other         1       65

etc.

In excel, how do i change the colors and marker symbols used based on the ID and vehicle group. I would like to change colors based on ID, and use a different symbol for each vehicle group (i.e car is circle, motorbike is triangle, and other is cross)

I don't know VBA, so if theres a way to do this through the menus, that would be appreciated

Answer 1

Just make sure you add each series separately. Here are the steps (assume Excel 2007 or greater):

1. Insert > Scatter
2. Click the chart > Design > Select Data
3. Add first series. Series Name = the cell containing 000. Series X Values = cells for count 5, 20, and 1. Series Y values = cells for mpg 10, 100 25.
4. Add the second series. Series Name = cell containing 001. Series X Values = cells for count 30, 28, 85. Series Y values = cells for mpg 60, 45, 35.
5. Add the third series according to above.

Following that process gives me:

A little clean-up and formatting, gives me: