Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 29. October 2009 10:05

Recently, a client asked me if it was possible to create an Excel scatter plot of his products, adding a label on each data point, and using different colors and symbols for different types of products. You could think of this as plotting 5 dimensions at once, instead of the usual two.

I quickly coded a VBA macro to do that, with a sample workbook to illustrate the usage. The macro is pretty rough, but was sufficient for my needs as is, so I haven’t put extra efforts in: feel free to improve upon it…

Here is a sample of the output:PowerScatterPlot

To use it, you will need to enter the values for the chart in 5 columns, anywhere in the worksheet. Columns 1 and 2 contain the X and Y values for the scatter plot, column 3 the labels you want to add to each data point, column 4 and 5 are integers which represent the code for the marker symbol and color for the data point. Columns 4 and 5 are clearly not an elegant solution, and you’ll probably have to play with the values until you find what you want.

Note that you have to fill in all fields, for every point of the chart.

PowerScatterPlotUsage

Once you have your data, simply select the entire range, all 5 columns of it, not including the headers, and click the “Generate ScatterPlot” button – et voila!

Download the ScatterPlot Workbook.

Comments

8/31/2010 12:06:21 PM #

ann eilleen miller baker


I copied the first 2 data rows into my excel file; expecting to see two points, but had four points (or 8 points);
i avoid macros (which I don't know how to use); just copied lines beginning 17% and 23%.

my objective is making an excel graph with 3 different symbols to describe 3 groups for which i
have x and y data ( a scattergram with 3 different symbols); i've been looking for about 10mins,
your treatment comes the closest

ann eilleen miller baker United States | Reply

5/12/2011 3:33:38 AM #

Mike Reynolds

Couldn't get this to work reliably in Excel 2007.  Any tips?

Mike Reynolds United States | Reply

5/15/2011 2:58:30 AM #

mathias

Sorry to hear that! Can you give me some details on the problems you are experiencing, so that I can look into it?
Mathias

mathias United States | Reply

6/21/2011 5:52:02 AM #

KG

Great tutorial.
However, I have some problem with my scattering graph. I have too many data label on my graph and they overlapped each other. I have to manually move the labels all the time. Is there any nice macro code to do like adjusting those overlap labels so they all can fit in nicely?

KG United States | Reply

6/27/2011 9:01:49 AM #

Mike Reynolds

I have the same issue with many labels.  Very hard to move them around.  My solution is to paste into Powerpoint as a Metafile, then Ungroup, Ungroup again, delete the white background, and then move around in PPT (hold down CTRL and move around the text).  Probably the easiest way.  

Optimizing this in Excel would be interesting, but it would be very complex.

Mike Reynolds United States | Reply

7/18/2011 6:47:28 AM #

Fontaine

Hi Mathias,

I could open the scatter plot and change the data in the first two columns, but I couldn't rerun the macro. When i pushed "Draw ScatterPlot" I got an error message that said: "The macro 'PowerScatterPlot.xls! DrawScatterplot' cannot be found."

I've never used macros so I'm not really sure what to do. I opened the spreadsheet normally, and I made sure NOT to disable macros upon opening. I'm using Microsoft excel 2008 on a Mac. Any ideas?

Fontaine United States | Reply

7/18/2011 6:55:35 AM #

Fontaine

oops. figured it out. nevermind!

Fontaine United States | Reply

7/18/2011 7:50:28 AM #

Fontaine

would you consider rebuilding this macro in applescript?

Fontaine United States | Reply

7/20/2011 3:58:10 AM #

Mathias

Hi Fontaine,
Glad you got it to work! Unfortunately, I do not have a Mac, and don't know anything about AppleScript, so I do not think this conversion will be happening any time soon. Maybe would be a good project for you? ;)
Mathias

Mathias | Reply

8/22/2011 1:09:48 AM #

Roger Hurst

I want to label points on a chart in an application I've coded in c#.  Thanks for any help.
Roger

Roger Hurst United States | Reply

8/11/2011 2:11:36 AM #

Misho

It would be awesome if you had a 6'th column for relative market size Smile

Misho United States | Reply

9/22/2011 5:53:20 AM #

Antonio

The column of the weight would be useful, as in the bubble chart.

Antonio Italy | Reply

12/12/2011 6:09:40 AM #

Chris J

Hey thanks for the Macro, I've made some tweaks and it still works out great for me.\

But i was wondering if you know of a place that shows all of the "Symbol codes" and their corresponding symbols as you refer to them.

All of my online searches are bringing up the ASCII codes so please let me know if you have any other resources.

Thanks

Chris J United States | Reply

12/12/2011 8:45:32 AM #

Chris J

i think i figured it out, if i'm correct there are only 9 types?

Chris J United States | Reply

12/26/2011 4:57:39 AM #

Nagaraj

Does anyone know how to use a label (text or number) INSTEAD of a marker symbol (such as square, circle etc) in EXCEL charts? Imanaged to do this many years ago, but have now forgotten.

Nagaraj United States | Reply

1/18/2012 12:15:25 PM #

pingback

Pingback from whatwouldjenniferdo.com

What Would Jennifer Do  » Blog Archive   » BBW 50. Don’t Google it.

whatwouldjenniferdo.com | Reply

3/9/2012 8:28:01 AM #

Bev G

I am trying to put a set of data into a scatterplot, but want to color code (or use different symbols) based on membership in one of four groups.  How can that be done in Excel 2010?

Bev G United States | Reply

3/20/2012 4:59:12 PM #

overtime wages

Golf, Delta and Charlie

overtime wages United States | Reply

6/28/2012 12:06:35 PM #

pingback

Pingback from fittravelsynonym.radioonthenet.mobi

Asteroid hunters announce first private deep space mission | Fit Travel Synonym

fittravelsynonym.radioonthenet.mobi | Reply

2/27/2013 11:28:14 AM #

Jack Bull

It is working fine for me, I tested in Excel XP, and in Excel 2008, I'm curious why others had troubles with it

Jack Bull United States | Reply

3/12/2013 8:20:32 AM #

pingback

Pingback from onestopanalytics.wordpress.com

Coloring the scatter plot in Excel in groups | One Stop Analytics

onestopanalytics.wordpress.com | Reply

3/12/2013 8:51:53 AM #

Roger

I built an excel app using c# where the scatter plot potentially spills onto multiple sheets. What I've not been able to solve is how to keep the scale on subsequent pages looking the same as on the initial page.   The first page has many plots which results in a pretty tight fit along the x axis.  However, on the following page, with much fewer points, the scale on the x axis is much further apart.  There must be some control to make the x axis calibration uniform across all sheets.  Can you help?

Roger United States | Reply

3/22/2013 3:24:56 PM #

Mathias

I haven't tried it, but I would approach this by retrieving the Axis / scale information from your first chart, and apply it to the other charts.

Mathias | Reply

3/23/2013 12:32:00 AM #

Roger

Not sure how you do that. Can you give more specifics?  Thanks much.

Roger United States | Reply

4/3/2013 3:42:28 AM #

Mathias

Hi Roger,
I don't have the time right now for a detailed write-up on this, but I tried out recording a Chart Axis manipulation in VBA (changing the min and max values on the Axis, the scale, etc...) - this should give you good hints for where to start, and what objects / properties are involved:
    ActiveChart.ChartArea.Select
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).MinimumScale = 0
    ActiveChart.Axes(xlCategory).MinimumScale = -1
    ActiveChart.Axes(xlCategory).MaximumScale = 0.9
    ActiveChart.Axes(xlCategory).MaximumScale = 1.5
    ActiveChart.Axes(xlCategory).MajorUnit = 0.1
    ActiveChart.Axes(xlCategory).MajorUnit = 0.25
Essentially I would first retrieve the MinimumScale and MaximumScale value on each of the Axes, and then apply it to the other charts...
Hope this helps - Cheers,
Mathias

Mathias United States | Reply

4/2/2013 9:40:07 AM #

Ron

I'm also interested in the solution to Roger's issue.

Ron United States | Reply

8/4/2013 2:09:45 AM #

pingback

Pingback from com-lab.biz

scatter plot with different point colours and sizes | user89

com-lab.biz | Reply

9/4/2013 4:47:47 PM #

Ron

Mathias,

That was just what I was looking for.  Thanks.

Ron United States | Reply

9/6/2014 10:42:26 PM #

pingback

Pingback from x139690.xiagao.us

Coloring the scatter plot in Excel in groups | 123

x139690.xiagao.us | Reply

12/6/2014 2:38:23 PM #

pingback

Pingback from congyug.net

how to add labels to the markers in different curves in the same graph | Zolman Answers

congyug.net | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS