Color code data points on a scatter chart (2007)

**JayUSA** · 06-12-2009, 11:25 PM

Hi Everyone,

Does anyone know if there's a way to color code points on a scatter chart based on their values in a "3rd dimension" (i.e. other than the 2 dimensions represented by the chart axes).

You might imagine a scatter chart that plots transactions by price (y) and units purchased (x).

In that scenario, it might also be nice to color code each point by the region that made the sale.

The only way I know to do this is to manually create separate columns of data for each region, so that each region can be plotted on the scatter chart as a separate data series.

But since I'd like to experiment with several color coding alternatives, I'm wondering if there isn't a simpler way...

For instance, Rob Bovey's "XY Chart Labeler" can add data point labels to a scatter chart...

Is there, perhaps, another add-in that might enable color coding of data points based on a 3rd set of values?

Just wonderin'...

Cheers,

Jay

**Andy Pope** · 06-13-2009, 09:28 AM

In can use code to set the colour of each point based on a set of value bands.

Please Login or Register  to view this content.

In this case C2:C6 contains a value between 0 and 10 that has 1 of 3 colours assigned.

**JayUSA** · 06-13-2009, 04:19 PM

Andy!

Again, thank you very much for your thoughtful and detailed response to my question!

I have downloaded your spreadsheet, and I am keen to use it as a model.

However, I find that when I change the values in "3rd Value" column, the colors of the scatter points don't change.

In other words, the color of the scatter points may have initially correlated with the values of "3rd Value", but they don't stay synced.

Do I need to explicitly run or refresh the VBA script somehow? I'm afraid, I'm not VBA literate so I really don't know.

Cheers,

Jay

**Andy Pope** · 06-14-2009, 06:31 AM

Yes you will need to run the macro again in order for it to colour based on the new data.

ALT+F8 will display the macro dialog, where you can select the macro and run it.

You can assign the macro to a shape or button to make it easier to refresh.

**Excel Matic** · 06-14-2009, 12:24 PM

Originally Posted by JayUSA

Andy!

However, I find that when I change the values in "3rd Value" column, the colors of the scatter points don't change.

Cheers,

Jay

You can use it as a sub with worksheet change event. That will run this module whenever data on the worksheet changes. But note that when anything (means ANYTHING) changes on the worksheet, this module will execute.

Or a better way is to change this macro to change only when a particular range is updated, and this range would be the cells where your data lies (I have not seen the attached excel so could not specify which columns and which rows)

**JayUSA** · 06-14-2009, 04:01 PM

Great Guys!

Now I get it! Very helpful & problem solved!

Thanks again, very much!

Jay

**JayUSA** · 06-14-2009, 09:28 PM

First of all.... a big THANK YOU to Andy and Mohit.

Second... I thought I was all set-to-go, but then I ran into lots of problems actually trying to apply this VBA code to my worksheet. The problems started as soon as converted the chart from an object to a sheet. Then I got even more confused...

So.. I am now attaching a very, very simple and very clean micro-version of my spreadsheet.

If one of you, or someone else, could get the scatter plots to receive a color according to their source ("source1", "source2",etc...), I would indeed be very, very grateful.

Since I have next to zero VBA skills, I think it will be much more instructive for me to see how this code is applied to my specific situation, than to try to blindly apply it myself.

Again, I could not be more grateful!

Cheers,

Jay

**Andy Pope** · 06-15-2009, 04:06 AM

Please Login or Register  to view this content.

**JayUSA** · 06-15-2009, 09:10 PM

Hi Andy,

I am so grateful and impressed by your assistance that I just had to find and read your bio to learn who you are!

And then I had to go look-up the word "bespoke" -- as in "bespoke solutions"!

Anyway, as you can probably tell, I'm astonished by the degree of interest and assistance that continuously flows from you, Martin, DonkeyOte and the other forum heavy-hitters.

Anyway... back to business...

I was very successful in my effort to import the "bespoke" VBA code you kindly supplied, and it is up and running well in my spreadsheet -- beautiful!

Well, beautiful with a hitch -- the hitch is that my data table contains hundreds of rows of data, and I use Autofilters to identify the data I wish to plot at any time on my scatter chart.

Unfortunately, the code that you customized for me does not work properly with respect to plotting a subset of data using Autofilters. And I don't understand enough about the code's internal logic to hypothesize about the specific nature of the problem.

I really feel like you have already provided far more assistance than I could reasonably hope to receive -- but I am just wondering if there is an easy modification that will allow your code to live happily in my environment.

Lastly, though I'm sure you understand the issue I describe, you can see it in vivo by taking a peek at my updated sample spreadsheet. It's all very self-explanatory.

In any event, thanks again for your extra-ordinary help with this issue.

Cheers,

Jay

**Andy Pope** · 06-16-2009, 05:19 AM

The code requires a tweak in order to handle filtered range.

This change moves through the Areas and Cells of the visible range.

Please Login or Register  to view this content.

**Bernadine** · 10-25-2012, 01:43 PM

Hi Andy,

Your code has helped me greatly. Thanks so much.
I got my scatter chart all color coded. Now I need to make a legend to show the meaning of each color. Do you have a code that make this legend?

Thanks So much.

Originally Posted by Andy Pope

Please Login or Register  to view this content.

**protonLeah** · 10-26-2012, 02:11 AM

Bernadine,
Welcome to the Forum, unfortunately:

1. Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

2. Please do not quote whole posts.

Color code data points on a scatter chart (2007)

LinkBack

Thread Tools

Rate This Thread

Display

Color code data points on a scatter chart (2007)

Re: Color code data points on a scatter chart (2007)

Re: Color code data points on a scatter chart (2007)

Re: Color code data points on a scatter chart (2007)

Re: Color code data points on a scatter chart (2007)

Re: Color code data points on a scatter chart (2007)

Re: Color code data points on a scatter chart (2007)

Re: Color code data points on a scatter chart (2007)

Re: Color code data points on a scatter chart (2007)

Re: Color code data points on a scatter chart (2007)

Re: Color code data points on a scatter chart (2007)

Re: Color code data points on a scatter chart (2007)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions