X Y SCATTER PLOT IN EXCEL HOW TO
X Y SCATTER PLOT IN EXCEL CODE
X Y SCATTER PLOT IN EXCEL SERIES
X Y SCATTER PLOT IN EXCEL DOWNLOAD

There is a second way to define our data. Normally we do use the horizontal axis as the more independent variable, but it is not mandatory, and this chart illustrates that fact.

It is somewhat natural to use the horizontal axis as the independent variable, but intuitively population seems less reliant on GDP than GDP is on population. Since GDP was in the first column in our range, B2:D21, it is plotted in the X-axis.

X Y SCATTER PLOT IN EXCEL SERIES

Population relationship and the second series shows the GDP vs. Notice the first series shows the GDP vs. This method will automatically add multiple series to our scatter plot and will populate our blank chart like the one shown here:īasic Scatter plot with GDP on the horizontal and population or mobile phone data on the vertical axis SetSourceData source : = Range ( 'B2:D21' )

The simple way uses the first (left-most column) variable as the horizontal axis and subsequent columns as vertical axis variables, like this: There are two ways to assign data to a scatter plot using VBA: (1) the simple way and (2) the targeted, granular way. If we connected our data with lines, we would see a bunch of confusing intersecting lines. In our dataset, some countries have big GDPs and small populations or big populations and smaller GDPs. If we have data that is always decreasing or increasing for each iteration, lines can be useful but they’re rarely necessary. For our data (and most data), it is useful to use scatter plots without lines. Regular scatter plots only have points with no lines connecting them. That way you won’t have sharp changes in direction in your plot. Scatter plots with Lines have straight, jagged lines between the points, while Smooth types allow Excel to approximate a smoothly-curving line between the data points. Markers are the symbols that appear at each point. The last two types also have a “no markers” option: xlXYScatterSmoothNoMarkers and xlXYScatterSmoothNoMarkers. These three scatter chart types can be specified using the ChartType property: There are three fundamental scatter plot choices. Of course, if you have others, you can simply change the ChartObjects index to another integer. This short VBA snippet assumes the first chart object you have on the sheet is the scatter chart you want to access. You won’t be able to tell it’s a scatter plot since we haven’t given it any data, but you’ll notice we explicitly changed the ChartType to xlXYScatter.

X Y SCATTER PLOT IN EXCEL CODE

The following code block will create a small chart and immediately convert it to an XY-scatter chart.

This is why scatter plots are so useful for answering data questions in all sorts of fields - from engineering to medicine to business. You may even find your clustering in your data, where datapoints are grouped together with empty space between the groupings.īy adding your data to scatter plots, all these correlations become immediately noticeable without you having to perform any mathematical correlation functions. Sometimes the datapoints are just randomly scattered around and we have no correlation. The larger one value is, the smaller the other value is. On the other hand, if they move left and down, it is a negative correlation. If the datapoints move left and upward, we can assume larger values of Variable X usually indicate larger values of Variable Y. Scatter plots are great for comparing how two variables relate to each other, since we can quickly see whether correlation exists between them or not. Scatter plots have a numerical (or date) value for both axes, whereas most other charts have a numerical axis and a categorical axis (like a country or department name). In our automating Excel charts with VBA tutorial, we talked about switching the axes between horizontal and vertical, and we mentioned scatter plot axes don’t behave the same. xlsm format, if you want to keep your work. Once your file’s open, you can save it in an Excel.

X Y SCATTER PLOT IN EXCEL DOWNLOAD

Just download the file and open it in Excel, so it looks like this: Nevertheless, it is sufficient for illustration purposes and it’ll be a fun example.įor convenience, we’ve extracted all our sample data and placed it in a CSV file for you. The data is somewhat asynchronous, especially as the mobile phone data was gathered in the 2010s - a decade of rapid growth in mobile. To demonstrate VBA scatter plot control in this tutorial, we’re going to use nominal GDP, national population, and mobile phone ownership data. More importantly, we’ll demonstrate why controlling a scatter plot with VBA can be so powerful. In this tutorial, we will take an in-depth look at how scatter plots are different and how you can control them using VBA in Excel. If you recall, we said scatter plots are a bit different from most other chart types.

X Y SCATTER PLOT IN EXCEL HOW TO

A couple weeks ago we described how to create Excel charts with VBA.

X y scatter plot in excel