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.


Download Practice Workbook

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


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.


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

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.

Sample Data

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.

2 Easy Approaches to Create a Scatter Plot in Excel

Step 2:

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

2 Easy Approaches to Create a Scatter Plot in Excel

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.

Sample Data

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

2 Easy Approaches to Create a Scatter Plot in Excel

Read More: How to Create a Scatter Plot with 4 variables in Excel (with Quick Steps)


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.

2 Easy Approaches to Create a Scatter Plot in Excel

Step 2:

  • Go to Develope tab.
  • Click on the Visual Basic option.

2 Easy Approaches to Create a Scatter Plot in Excel

Step 3:

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

2 Easy Approaches to Create a Scatter Plot in Excel

Step 4:

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

Sample Data

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.

2 Easy Approaches to Create a Scatter Plot 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.

2 Easy Approaches to Create a Scatter Plot in Excel

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.

Read More: How to Create a Scatter Plot in Excel with 3 Variables (with Easy Steps)


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.


Related Articles

Bishawajit Chakraborty

Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo