Scatter plots are generally a nice tool to represent data, especially ones where we’d like to show a pattern of some kind. But what happens when we have more than 2D data for a scatter plot?
Let’s take an example here. This is how the data looks like –
While the above scatter plot gives a general idea on the overall trend of the data, we’re unable to plot the 3rd dimension in the chart directly – Region grouping. But this can be achieved in Excel with a little exercise in VBA.
This is how we want our chart to look like –
VBA Code that makes this possible –
With ActiveChart.SeriesCollection(1) For i = 1 To rows With .Points(i) .MarkerStyle = pointSymbols.Cells(i, 1).Value2 .MarkerBackgroundColorIndex = pointColors.Cells(i, 1).Value2 .MarkerForegroundColorIndex = pointColors.Cells(i, 1).Value2 .markerSize = sizeOfMarker End With Next End With
Another way to achieve the same is –
For Each pt In ActiveSheet.ChartObjects(1).SeriesCollection(1).Points With pt.Format.Fill .Visible = msoTrue .ForeColor.RGB = RGB(255, 0, 0) End With Next
Here’s a neat link that shows how this can be done – http://www.clear-lines.com/blog/post/Excel-ScatterPlot-with-labels-colors-and-markers.aspx