Scatter plot - Size of data points from spreadsheet value?

[ferdy]

I have an XY scatter graph with some points in it.

My data in the spreadsheet looks like this:

[Title] [X-value] [Y-value] [size]

Data 1 | 10 | 10 | 1
Data 2 | 33 | 44 | 5

What I want to do is to change the size of the data point according to
the value I specify. Right now, I have to individually modify each
point and add a number corresponding to the size.

Is there anyway to take the size-value from the spreadsheet and change
the size of the point in the graph?

In the long run, I am also looking to modify the color.

If the solution requires some VBA coding, I am up for that. Any
suggestions are valuable!

 

[Andy Pope]

Hi,

Try this code. Note that your example sizes will cause a problem. The
marker size is a integer value between 2 and 72.

The code also picks up the interior colour of the size cell so make sure
you apply a fill colour otherwise the markers will disappear.

'------------------------------------------
Sub SizeMarker()

Dim rngSizes As Range
Dim lngIndex As Long

' range containing marker size
Set rngSizes = Range("E4:E5")
With ActiveChart
With .SeriesCollection(1)
For lngIndex = 1 To .Points.Count
With .Points(lngIndex)
.MarkerSize = rngSizes.Cells(lngIndex).Value
.MarkerBackgroundColorIndex = _
rngSizes.Cells(lngIndex).Interior.ColorIndex
.MarkerForegroundColorIndex = _
rngSizes.Cells(lngIndex).Interior.ColorIndex
End With
Next
End With
End With

End Sub
'------------------------------------------

Have you looked at the Bubble chart? You can use a custom marker with
these and changing size will be linked to the cell. Colour on the other
hand will require multiple series and some conditional formula.

Here is some more information on custom markers.
http://peltiertech.com/Excel/ChartsHowTo/CustomMarkers.html

Cheers
Andy

I have an XY scatter graph with some points in it.

My data in the spreadsheet looks like this:

[Title] [X-value] [Y-value] [size]

Data 1 | 10 | 10 | 1
Data 2 | 33 | 44 | 5

What I want to do is to change the size of the data point according to
the value I specify. Right now, I have to individually modify each
point and add a number corresponding to the size.

Is there anyway to take the size-value from the spreadsheet and change
the size of the point in the graph?

In the long run, I am also looking to modify the color.

If the solution requires some VBA coding, I am up for that. Any
suggestions are valuable!

 

[ferdy]

Hi Andy,

Your solution works fine (after some tweaking)! I have several datasets
with only one point value so I need to loop through the datasets as
well. Anyway, it turned out great.

Your suggestion on bubble charts is interesting, i'll look into that.
Thanks again!