vba - How can I color dots in a xy scatterplot according to column value?

Question

Consider the following worksheet:

     A       B        C        D
1 COMPANY  XVALUE   YVALUE   GROUP
2 Apple     45       35       red
3 Xerox     45       38       red
4 KMart     63       50       orange
5 Exxon     53       59       green

I have used the scatterplot function in Excel to create the following chart:

However, each point in the chart has an additional property: GROUP. There are four groups: red, orange, black and green. I would like to color each dot accordingly, so that I could perhaps see a pattern (group greenbeing almost always on the left side of the chart, for instance). Because my list is 500 rows long, I cannot do this manually. How can I do this automatically?

Answer 1

Non-VBA Solution:

You need to make an additional group of data for each color group that represent the Y values for that particular group. You can use these groups to make multiple data sets within your graph.

Here is an example using your data:

     A       B        C        D                    E                        F                            G
----------------------------------------------------------------------------------------------------------------------
1| COMPANY  XVALUE   YVALUE   GROUP                 Red                     Orange                       Green
2| Apple     45       35       red         =IF($D2="red",$C2,NA()) =IF($D2="orange",$C2,NA()) =IF($D2="green",$C2,NA())
3| Xerox     45       38       red         =IF($D3="red",$C3,NA()) =IF($D3="orange",$C3,NA()) =IF($D3="green",$C3,NA())
4| KMart     63       50       orange      =IF($D4="red",$C4,NA()) =IF($D4="orange",$C4,NA()) =IF($D4="green",$C4,NA())
5| Exxon     53       59       green       =IF($D5="red",$C5,NA()) =IF($D5="orange",$C5,NA()) =IF($D5="green",$C5,NA())

It should look like this afterwards:

     A       B        C        D          E           F          G
---------------------------------------------------------------------
1| COMPANY  XVALUE   YVALUE   GROUP       Red         Orange     Green
2| Apple     45       35       red         35         #N/A       #N/A    
3| Xerox     45       38       red         38         #N/A       #N/A
4| KMart     63       50       orange     #N/A         50        #N/A
5| Exxon     53       59       green      #N/a        #N/A        59

Now you can generate your graph using different data sets. Here is a picture showing just this example data:

You can change the series (X;Y) values to B:B ; E:E, B:B ; F:F, B:B ; G:G respectively, to make it so the graph is automatically updated when you add more data.