A couple weeks ago we described how to create Excel charts with VBA. If you recall, we said besprinkle plots are a bit different from most other chart types. In this tutorial, we will take an in-depth look at how besprinkle plots are different and how yous can control them using VBA in Excel. More importantly, we'll demonstrate why decision-making a scatter plot with VBA can exist so powerful.

To demonstrate VBA scatter plot control in this tutorial, we're going to utilise nominal GDP, national population, and mobile phone buying data. The data is somewhat asynchronous, especially every bit the mobile telephone data was gathered in the 2010s - a decade of rapid growth in mobile. Nevertheless, it is sufficient for illustration purposes and information technology'll be a fun example.

For convenience, nosotros've extracted all our sample information and placed it in a CSV file for you lot. Just download the file and open up it in Excel, then it looks like this:

Data for VBA Scatter Plot Tutorial

Once your file's open up, you can save it in an Excel .xlsm format, if you want to keep your work.

  • Excel Besprinkle Plots
    • Introduction to Correlation
  • Creating a Besprinkle Plot with VBA
    • Smooth, Lines, or Markers But
  • Choosing the Data
    • The XValues and Values Properties
    • Adding and Deleting Series
  • Data Labels
    • Axes
    • Data Points
  • Higher Dimensional Information
  • Conclusion

Excel Scatter Plots

In our automating Excel charts with VBA tutorial, we talked about switching the axes between horizontal and vertical, and we mentioned besprinkle plot axes don't behave the same. Scatter plots have a numerical (or date) value for both axes, whereas most other charts take a numerical centrality and a categorical axis (like a state or department proper noun).

Introduction to Correlation

Scatter plots are great for comparison how two variables relate to each other, since nosotros tin quickly see whether correlation exists betwixt them or not. If the datapoints move left and up, we tin assume larger values of Variable X unremarkably betoken larger values of Variable Y. This is positive correlation. On the other hand, if they move left and downwards, information technology is a negative correlation. The larger one value is, the smaller the other value is.

Sometimes the datapoints are simply randomly scattered around and we have no correlation. This is nonetheless valuable noesis!

You lot may even find your clustering in your data, where datapoints are grouped together with empty space betwixt the groupings.

Past adding your data to scatter plots, all these correlations become immediately noticeable without yous having to perform whatever mathematical correlation functions. This is why scatter plots are so useful for answering data questions in all sorts of fields - from engineering science to medicine to concern.

Creating a Scatter Plot with VBA

The following code block will create a small chart and immediately catechumen it to an XY-besprinkle chart. You won't exist able to tell information technology'south a scatter plot since nosotros haven't given it any information, but you lot'll detect nosotros explicitly changed the ChartType to xlXYScatter.

                          Sub              generate_scatterplot              ()              Dim              ochartObj              Every bit              ChartObject              Dim              oChart              As              Chart              Prepare              ochartObj              =              ActiveSheet              .              ChartObjects              .              Add              (              Meridian              :              =              10              ,              Left              :              =              325              ,              Width              :              =              600              ,              Height              :              =              300              )              Set              oChart              =              ochartObj              .              Chart              oChart              .              ChartType              =              xlXYScatter              Stop              Sub

Make powerful macros with our free VBA Programmer Kit

This is actually pretty neat. If yous have trouble understanding or remembering it, our free VBA Developer Kit can help. Information technology'southward loaded with VBA shortcuts to help you make your ain macros similar this one - we'll send a copy, along with our Big Volume of Excel VBA Macros, to your e-mail accost below.

Over again, this macro will generate a bare chart on the current canvass, change its type to a scatter plot (or xlXYScatter type in VBA-speak), and store the nautical chart object in the variable oChart so we tin easily dispense it later. You lot can play with the 4 numbers in the .Add statement to move the chart around and resize information technology.

If you stop the macro and demand to gear up your oChart variable over again, you can use this line instead of always creating a brand new nautical chart:

                          Set up              oChart              =              ActiveSheet              .              ChartObjects              (              1              ).              Chart

This brusque VBA snippet assumes the first chart object y'all have on the canvass is the scatter chart y'all want to access. Of course, if you have others, you can simply change the ChartObjects alphabetize to another integer.

Shine, Lines, or Markers Only

There are three cardinal scatter plot choices. These three besprinkle nautical chart types can be specified using the ChartType property:

  • xlXYScatter
  • xlXYScatterLines
  • xlXYScatterSmooth

The terminal two types likewise have a "no markers" pick: xlXYScatterSmoothNoMarkers and xlXYScatterSmoothNoMarkers. Markers are the symbols that appear at each point.

Scatter plots with Lines accept straight, jagged lines between the points, while Smooth types allow Excel to approximate a smoothly-curving line between the data points. That mode y'all won't have abrupt changes in management in your plot. Regular besprinkle plots simply accept points with no lines connecting them. For our information (and nearly data), it is useful to utilize scatter plots without lines.

If nosotros have data that is always decreasing or increasing for each iteration, lines can be useful but they're rarely necessary.

In our dataset, some countries accept big GDPs and small populations or big populations and smaller GDPs. If nosotros connected our data with lines, we would see a agglomeration of confusing intersecting lines.

Choosing the Data

There are two ways to assign data to a besprinkle plot using VBA: (1) the uncomplicated manner and (ii) the targeted, granular way.

The unproblematic way uses the start (left-almost cavalcade) variable as the horizontal axis and subsequent columns as vertical axis variables, like this:

                          oChart              .              SetSourceData              source              :              =              Range              (              "B2:D21"              )

This method will automatically add multiple serial to our scatter plot and will populate our blank nautical chart like the one shown here:

Scatter plot with GDP, population, and phone data
Basic Scatter plot with Gross domestic product on the horizontal and population or mobile telephone data on the vertical axis

Notice the first series shows the GDP vs. Population relationship and the 2nd series shows the GDP vs. Mobile Telephone human relationship. Since Gross domestic product was in the first cavalcade in our range, B2:D21, it is plotted in the 10-axis.

It is somewhat natural to use the horizontal axis as the contained variable, but intuitively population seems less reliant on Gross domestic product than GDP is on population. Normally we do utilize the horizontal centrality equally the more than independent variable, only it is not mandatory, and this chart illustrates that fact.

The XValues and Values Properties

There is a second way to define our data. This method gives you a lot more than control over your scatter charts. Since we only have iii columns of data, we don't necessarily demand more control but in that location are many situations where you would desire this level of control. In our information, we take a Gdp vs. Population relationship and a Gdp vs. Mobile Phone relationship. Both use Gross domestic product equally the base, only imagine if we had more columns of data to plot.

If we had several columns of information, we might want to use different data for the x-centrality and dissimilar data for the y-axis for each grouping, similar GDP vs. Population, Phones vs. Cars, and Cyberspace Subscribers vs. Emissions. The XValues and Values backdrop permit you lot control the plotted axes individually.

Note: If you have several serial with different relationships, you will probably need to scale them or place similar data sizes on the primary centrality and other similar data on the secondary axis. Otherwise, you might end up with clustered results yous won't be able to describe whatsoever conclusions from.

Since the magnitude of country population and mobile subscribers are similar, let'south set our plotted data with those two as the base and GDP every bit the y-variable.

                          oChart              .              SeriesCollection              (              1              ).              XValues              =              Range              (              "C2:C21"              )              oChart              .              SeriesCollection              (              one              ).              Values              =              Range              (              "B2:B21"              )              oChart              .              SeriesCollection              (              2              ).              XValues              =              Range              (              "D2:D21"              )              oChart              .              SeriesCollection              (              2              ).              Values              =              Range              (              "B2:B21"              )

These lines of lawmaking volition only piece of work if you've already added 2 series of data.

XValues are the horizontal axis numerical data and Values are the vertical centrality numerical data. The two properties should always be specified in pairs. The integer in the SeriesCollection represents which series of data you lot're trying to command. When you lot run the code, your nautical chart will look like this:

Same scatter plot above but inverted axes
Besprinkle plot with population and mobile phones on the horizontal axis and Gdp on the vertical axis

Adding and Deleting Series

The SeriesCollection object is our two-columned series of data. If you want to add some other serial after creating your chart, use a snippet like this:

                          oChart              .              SeriesCollection              .              Add              Source              :              =              Range              (              "B2:B21"              )

This VBA lawmaking will add a 3rd series in our example and it will set our range B2:B21 equally the XValues. Excel volition guess that nosotros want to utilize C2:C21 every bit the Values range. It will guess differently based on the calibration of your data, so it's a good idea to arrange both the XValues and Values manually after adding a series.

                          oChart              .              SeriesCollection              (              3              ).              XValues              =              Range              (              "D2:D21"              )              oChart              .              SeriesCollection              (              three              ).              Values              =              Range              (              "B2:B21"              )

Deleting a serial follows a like syntax:

                          oChart              .              SeriesCollection              (              iii              ).              Delete

This will simply delete the third series we just created on our chart.

Data Labels

Permit's go along with a chart that just shows the Gdp vs. Population relationship. To do that, nosotros'll delete SeriesCollection(ii), which graphed the Gdp vs. Mobile Telephone Human relationship. Let's also remove the United States, Prc, and India from our data since their populations and GDPs are outliers and crush most of our points into the lesser left corner.

While we're at information technology, allow's make sure the axes of our beginning series are displayed the manner we want them by using this code:

                          oChart              .              SeriesCollection              (              1              ).              XValues              =              Range              (              "B2:B21"              )              oChart              .              SeriesCollection              (              i              ).              Values              =              Range              (              "C2:C21"              )

Afterward making these changes, our nautical chart and data should wait like this:

Data and Chart without outliers
Chart and Data for Most of the Peak 20 Gdp/Population countries, with outliers removed

Labeling the Axes

Upward until at present, you've probably noticed we don't have a quick way to know what we're actually plotting. Titles for our axes would aid us out a lot. To requite the axes labels, nosotros would just set the chart AxisTitle captions.

                          oChart              .              Axes              (              xlCategory              ).              HasTitle              =              True              oChart              .              Axes              (              xlCategory              ).              AxisTitle              .              Caption              =              "Gross domestic product in Millions of USD"              oChart              .              Axes              (              xlValue              ).              HasTitle              =              True              oChart              .              Axes              (              xlValue              ).              AxisTitle              .              Explanation              =              "Population"

You tin also gear up the AxisTitle captions equal to a value stored in a particular cell, if y'all adopt.

                          oChart              .              Axes              (              xlValue              ).              AxisTitle              .              Caption              =              Range              (              "C1"              )

Labeling the Information Points

Currently we just accept a agglomeration of diamonds merely no idea which diamond goes with which state. This is fine if you simply desire to identify trends in your data, just you could label the datapoints to make each displayed datapoint more meaningful to your terminate user. Add the line

                          oChart              .              SeriesCollection              (              ane              ).              HasDataLabels              =              True

and nosotros get:

Chart with Data Labels but they are numbers

Unfortunately, our data labels are but numbers and are not particularly helpful. How can nosotros modify each signal's label to match the country name, instead? By modifying the Points object!

We tin iterate through each particular in the collection and apply text values to each 1. Before we exercise that, recollect we've removed the two outliers Red china and the USA, so points 1 and two are not present in the chart (neither is Bharat, bespeak vi). Nonetheless, they notwithstanding exist in the Points collection, so we're still able to iterate without having to worry about encountering errors.

                          For              countryRow              =              2              To              20              oChart              .              SeriesCollection              (              1              ).              Points              (              countryRow              -              1              ).              DataLabel              .              Text              =              Cells              (              countryRow              ,              1              ).              Value              Next              countryRow

and we end upwardly with this chart, which is much easier to read:

Chart with Countries as Data Point Labels

Naming the Series

Detect how in that location'due south a legend on the right side showing Series1. Renaming the serial to something more than meaningful is simple. One common way to proper noun a series is to employ the name of the relationship:

                          oChart              .              SeriesCollection              (              i              ).              Proper name              =              "Gross domestic product vs Pop"

or

                          oChart              .              SeriesCollection              (              1              ).              Name              =              Range              (              "B1"              )              &              "-"              &              Range              (              "C1"              )

The second method demonstrates how you can refer to cells in your series names. In our example, we actually concatenated two cells, though this makes the serial name quite long.

Since nosotros just have 1 series, we can completely remove the legend. A legend isn't necessary if yous're merely plotting 1 human relationship. If you lot have several series, though, using a legend is quite useful, equally viewers cannot deduce data categories.

It's besides worth point out that often when you name a serial, Excel will automatically add a championship to the top of your chart.

College Dimensional Information

Our screens are ii-dimensional and therefore nosotros can merely display two dimensional data on them (Okay… Or iii dimensional if you lot use a 3D chart). Despite this limitation, we can still brand our charts reflect higher dimensional data by changing the markers themselves. You usually meet this in the coloring, sizing, or styling of the markers.

For instance, if we wanted to show which countries had more mobile phones than people, we could easily summate that with a macro and apply a specific marker or color to those countries.

Let'due south make countries that have more people than phones red circles, as opposed to bluish diamonds. If the number of people is greater than the number of phones by xv%, let'due south also make those circles hollow. You would program this logic using a VBA snippet similar this:

                          For              countryRow              =              2              To              20              If              Cells              (              countryRow              ,              4              )              -              Cells              (              countryRow              ,              3              )              <              0              Then              oChart              .              SeriesCollection              (              1              ).              Points              (              countryRow              -              1              ).              MarkerStyle              =              xlCircle              oChart              .              SeriesCollection              (              1              ).              Points              (              countryRow              -              1              ).              MarkerBackgroundColor              =              vbRed              oChart              .              SeriesCollection              (              ane              ).              Points              (              countryRow              -              one              ).              MarkerForegroundColor              =              vbRed              If              Cells              (              countryRow              ,              3              )              /              Cells              (              countryRow              ,              4              )              >              1.xv              So              oChart              .              SeriesCollection              (              1              ).              Points              (              countryRow              -              1              ).              MarkerBackgroundColor              =              vbWhite              End              If              End              If              Adjacent              countryRow

The resulting plot is a little dense, but you tin hands extract information most an actress dimension by simply looking at the plot. If the population is greater than the number of phones, nosotros brand the marker a circle and color it red. The foreground colour volition show up every bit the outline and the background colour is the inner office. If the number of people exceeds the number of mobile phones by xv% nosotros make the background color (fill up or inner part) white.

Now let's put all these steps together into a terminal advanced VBA scatter plot creation macro.

                          Option              Explicit              Sub              create_advanced_vba_scatter_plot              ()              Dim              ochart              Every bit              Object              ,              ochartObj              As              Object              Dim              countryRow              As              Integer              ,              lastrow              Every bit              Integer              Set              ochartObj              =              ActiveSheet              .              ChartObjects              .              Add              (              Top              :              =              10              ,              Left              :              =              325              ,              Width              :              =              600              ,              Height              :              =              300              )              Set              ochart              =              ochartObj              .              Nautical chart              ochart              .              ChartType              =              xlXYScatter              'Set ochart = ActiveSheet.ChartObjects(1).Chart 'uncomment this and annotate the 3 lines above              'if nautical chart already created              ochart              .              SeriesCollection              .              Add              Source              :              =              Range              (              "B2:B21"              )              ochart              .              SeriesCollection              (              i              ).              XValues              =              Range              (              "B2:B21"              )              ochart              .              SeriesCollection              (              i              ).              Values              =              Range              (              "C2:C21"              )              ochart              .              Axes              (              xlCategory              ).              HasTitle              =              True              ochart              .              Axes              (              xlCategory              ).              AxisTitle              .              Explanation              =              "Gross domestic product in Millions of USD"              ochart              .              Axes              (              xlValue              ).              HasTitle              =              True              ochart              .              Axes              (              xlValue              ).              AxisTitle              .              Caption              =              "Population"              ochart              .              SeriesCollection              (              ane              ).              HasDataLabels              =              True              lastrow              =              Range              (              "D"              &              Rows              .              Count              ).              End              (              xlUp              ).              Row              For              countryRow              =              2              To              lastrow              ochart              .              SeriesCollection              (              1              ).              Points              (              countryRow              -              i              ).              DataLabel              .              Text              =              Cells              (              countryRow              ,              1              ).              Value              Next              countryRow              ochart              .              SeriesCollection              (              1              ).              Name              =              Range              (              "B1"              )              &              " vs. "              &              Range              (              "C1"              )              ochart              .              Legend              .              Delete              For              countryRow              =              two              To              lastrow              If              Cells              (              countryRow              ,              4              )              -              Cells              (              countryRow              ,              3              )              <              0              Then              ochart              .              SeriesCollection              (              one              ).              Points              (              countryRow              -              1              ).              MarkerStyle              =              xlCircle              ochart              .              SeriesCollection              (              i              ).              Points              (              countryRow              -              i              ).              MarkerBackgroundColor              =              vbRed              ochart              .              SeriesCollection              (              i              ).              Points              (              countryRow              -              1              ).              MarkerForegroundColor              =              vbRed              If              Cells              (              countryRow              ,              3              )              /              Cells              (              countryRow              ,              4              )              >              one.15              Then              ochart              .              SeriesCollection              (              1              ).              Points              (              countryRow              -              1              ).              MarkerBackgroundColor              =              vbWhite              Terminate              If              Stop              If              Adjacent              countryRow              End              Sub

You'll detect in this macro I added a bit of code to find the terminal used row with VBA.

When nosotros run this macro, we end up with this good-looking informative chart that conveys more than information than traditionally provided on an XY besprinkle plot:

Chart with different markers for different categories
A 2nd nautical chart that displays information in four dimensions (population, Gross domestic product, and two ratios about mobile phones-population)

Of course, y'all'd still demand to explicate to the viewer what all these colors and shapes hateful.

For more advanced scatter plots, you don't have to limit yourself to binary "yes or no" atmospheric condition. Another idea might exist to get the number of brain cancer cases for each state and so create a sliding scale for sizes or colors, where bigger or redder markers mean a college prevalence of brain cancer. A study like this is how graduate student and scientists attempt to correlate (or disprove) cell phone employ to disease prevalence.

You tin can observe the other marking properties in this Microsoft Documentation if you want to dig deeper and change other parts of the Bespeak objects.

Conclusion

In this tutorial, we gave yous a lot of neat VBA scatter plot tips and we even discussed how to customize the plots to convey more information than traditionally provided. Oftentimes with plots information technology's easier to use the GUI for one-off changes, but something like showing higher-dimensional information, like we just demonstrated, is really a good case where scripting up a few lines of VBA code tin make your scatter plot cosmos much easier, even if it is a one-time action.

Handling your scatter plots with VBA is also helpful when yous're trying to design all your plots with a compatible, consistent appearance. VBA will do that much better than the homo center would.

If you haven't already done and so, subscribe to my gratuitous wellsrPRO VBA Training Programme using the form below. Yous'll savor the unique VBA tips we ship your mode.