How to Create a Scatter Plot in Excel with 2 Variables

Microsoft Excel gives you the option to create a Scatter Plot in Excel when you require it to visualize the relationship between two sets of quantitative data. In this article, you will learn and see how to create a Scatter Plot or X-Y graph in Excel using 2 variables.

Watch Video – Create a Scatter Plot with 2 Variables in Excel

Scatter Plot in Excel

The association between two variables is depicted on a two-dimensional chart known as a scatter plot, also known as an X-Y graph. Both the horizontal and vertical axes of a scatter graph are value axes used to plot numerical data. The dependent variable is typically on the y-axis, whereas the independent variable is typically on the x-axis. Values at the point where the x and y-axis meet are shown as single data points on the graph. A scatter plot’s primary use is to display the strength of the correlation between the two variables. The correlation is larger when the data points fall more closely together along a straight line.

How to Create a Scatter Plot in Excel with 2 Variables: 2 Easy Approaches

In the given data set, we arrange the data in order to visualize the link between the advertising expenditure for a certain month as an independent variable and the number of products sold as a dependent variable by using the Charts option and applying the VBA Codes.

1. Using Charts Option to Create a Scatter Plot in Excel with 2 Variables

A scatter graph shows two connected quantitative variables, as we have already explained. You then input two sets of numerical information into two different columns. The following steps are given below.

Step 1:

First, select the Advertising Budget and No. of Products Sold columns.

Step 2:

Go to the Insert tab.
Click on the Scatter chart icon.

Step 3:

Select the desired option from the Scatter charts. Here, we will select the first option, which we have marked with a red color rectangle.

Finally, we will show the given result on the Scatter chart.

2. Applying VBA Code to Create a Scatter Plot in Excel with 2 Variables

Applying the VBA Code to demonstrate a relationship between two sets of quantitative data is a very convenient way. We will apply a VBA Code in Excel to create a Scatter Plot to visualize the connection between two variables. The following steps are given below

Step 1:

Let us consider the following data set to apply a VBA Code to create a Scatter Plot.

Step 2:

Go to Developer tab.
Click on the Visual Basic option.

Step 3:

A Visual Basic window will open and click on the Insert tab.
Click on the Module option to create a new Module.

Step 4:

Here, paste the following VBA Codes in the new Module.

Sub Create_scatterplot()
Dim scatterchart As Chart
Set scatterchart = Charts.Add
With scatterchart
.SetSourceData Source:=Sheets("Scatterplot").Range("C5:D16")
.ChartType = xlXYScatter
End With 
End Sub

Finally, we will see the following Scatter chart using VBA Codes in Excel.

Step 5:

You can view our VBA codes using the following method.
First, right-click on the Scatterplot sheet.
Click on the View Code option.

Notes:

When you will open the Visual basic window, you must create a new module to write your VBA code
When you will show the Scatter charts option, you must select any one of them according to your desired chart.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

Scatterplot 2 Variables.xlsm

Conclusion

In this article, I’ve covered two easy approaches to create a Scatter Plot in Excel. I sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

<< Go Back To Make Scatter Plot in Excel | Scatter Chart in Excel | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!