Coloring the scatter plot by groups in Excel

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 –

TableChart1

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 –

Chart2

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

Advertisements

6 thoughts on “Coloring the scatter plot by groups in Excel

  1. Jackqueline

    I came to your “Coloring the scatter plot in Excel in groups | One Stop Analytics” page and noticed you could have a lot more traffic. I have found that the key to running a website is making sure the visitors you are getting are interested in your subject matter. There is a company that you can get traffic from and they let you try it for free. I managed to get over 300 targetted visitors to day to my website. Visit them today: http://voxseo.com/traffic/

    Reply
    1. Arun Ravi Post author

      Hey Angelica, that used to be my blog. I recently changed my Blog name from “One Stop Analytics” to “Excelytics” since I was mostly focused on Excel. They are both one and the same! 🙂

      I tried reaching you on your email – if you can email me, we can see what works. Thanks!

      Reply
  2. web

    Oh my goodness! an amazing article dude. Thank you However I am experiencing issue with ur rss . Don’t know why Unable to subscribe to it. Is there anyone getting identical rss problem? Anyone who knows kindly respond. Thnkx

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s