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.


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.


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 #


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

mathias United States | Reply

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


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 #


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 #


oops. figured it out. nevermind!

Fontaine United States | Reply

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


would you consider rebuilding this macro in applescript?

Fontaine United States | Reply

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


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 | 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 Hurst United States | Reply

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


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 #


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.


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 #


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 from

What Would Jennifer Do  » Blog Archive   » BBW 50. Don’t Google it. | 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 from

Asteroid hunters announce first private deep space mission | Fit Travel Synonym | 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 from

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

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


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 #


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 #


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

Roger United States | Reply

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


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.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 United States | Reply

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


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

Ron United States | Reply

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


Pingback from

scatter plot with different point colours and sizes | user89 | Reply

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



That was just what I was looking for.  Thanks.

Ron United States | Reply

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


Pingback from

Coloring the scatter plot in Excel in groups | 123 | Reply

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


Pingback from

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

11/17/2015 11:57:37 PM #


Pingback from

VBA Code to Format Individual Data Points Based Upon Adjacent Cell Values | Reply

7/21/2016 12:00:42 PM #


Pingback from

How To Make A Scatterplot In Excel For Mac | Gogoodskh | Reply

Add comment

  Country flag

  • Comment
  • Preview


Comment RSS