Excel scatter chart - Markers tied to Data

[Prue]

Hi, </SPAN>

I am currently working on a stakeholder mapping and I am using a scatter chart in Excel to do so.</SPAN>
I succeeded in doing the mapping and placing the stakeholders according to the rating I gave them. However I would like the shape and colour of the markers to depend on the data in the table.</SPAN>
I have to columns (X,Y) that define the position of the stakeholder and I then have another column which, if possible, would define the colour of the marker (in this column for each stakeholder I can select from a drop down list the support level – ally, neutral or opponent – and I would like each of these options to correspond to a colour –green, grey, red- and by selecting ally I would get a green marker in the chart) finally the last column will define the shape of the marker – each shape corresponding to a type of stakeholder that can also be selected through a drop down list.</SPAN>

Please let me know if you have a solution to have the shape and colour of each marker tied with the data so that it changes automatically rather than clicking on each marker and formatting the colour/shape.</SPAN>

Many thanks,

Best,
Prue

 

[Damon Ostrander]

Hi Prue,

This can be done with a bit of VBA. It is difficult to describe this textually, but if you like I can send you a workbook that contains a couple of examples that I think you will find helpful. Just PM me with your email address and I'll send it to you.

 

[pgc01]

Hi Prue
Welcome to the board

Damon's solution should solve your problem using vba.

This would be another option, not using vba: split your data into several series (number of coulours x number of shapes) and format the series.

Although very easy, it would take some time to setup each series (depending on the total number of series), but this would be only the first time you define the chart and once per series. When the data changes the chart updates automatically and it will be easy to reuse the chart next time.