Unless your chart is extremely big and going to be shown on a very large screen, this is pretty easy to manage. Each of these data series, can only consist of 255 data points.This means that you can only have 255 distinct continuous lines on your chart. Each sequence of coordinates that join together to make a continuous line are defined as a data series. More specific to the advanced use of scatter plots, Excel limits you to 255 data series on a chart.Fortunately for myself, the client for the ‘March Chart’ had upgraded to at least Excel 2007 so this was not an issue for me. The object model used to create charts was changed from Excel 2003 to Excel 2007, so it is very important to be aware of this, and find out early if your client is using Excel 2003. For starters, there is the ever present issue of backward compatibility to Excel 2003.This all seems far too good, so Excel had to put some barriers in our way to make it a little trickier. With this functionality in mind, you essentially now have a blank canvas that you can draw anything on in Excel with VBA!

A series of sequential data points with straight lines joining them called a ‘data series’ can be used to create paths and even ‘curves’. Each data point can then be joined by a line to another data point, creating a straight line.

The scatter plot can be thought of as a blank canvas on to which x,y data points can be plotted. This template can then be twisted and abused into doing what you need through tricks and manipulation within VBA.įor the March Chart project, I discovered just how very useful a scatter plot can be as the template for an advanced Excel chart. When creating a custom chart in Excel, you must use one of the existing charts as a template.

I received a print out of an old ‘March Chart’ that had been manually created by the client, and was asked to replicate it exactly in Excel so that it could be viewed as an output of a mathematical model. More recently, I have been required to create a custom ‘March Chart’ that plots the movements of different work crews over space and time. I have in the past created L-Shaped Graphs for clients that helped optimise the use of space for a dynamic Excel Dashboard that needed to be constrained to an A4 print out.

These outputs are important to clients as they allow them to view the complex outputs in simple, familiar ways. I use VBA to create these custom charts in a dynamic way to visualise outputs of mathematical modelling. I am often required to create some very specific charts for clients that are far outside of the capability of regular Excel charting functionality.

Create a scatter plot in excel 2007