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.
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.
- First, select the Advertising Budget and No. of Products Sold columns.
- Go to the Insert tab.
- Click on the Scatter chart icon.
- 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
- Let us consider the following data set to apply a VBA Code to create a Scatter Plot.
- Go to Develope tab.
- Click on the Visual Basic option.
- A Visual Basic window will open and click on the Insert tab.
- Click on the Module option to create a new Module.
- 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.
- You can view our VBA codes using the following method.
- First, right-click on the Scatterplot sheet.
- Click on the View Code option.
- 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.
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.
- How to Add Vertical Line to Scatter Plot in Excel (2 Easy Methods)
- Add Horizontal Line in Excel Scatter Plot (2 Quick Methods)
- How to Make a Scatter Plot in Excel with Two Sets of Data (in Easy Steps)
- Add Text to Scatter Plot in Excel (2 Easy Ways)
- How to Add Line to Scatter Plot in Excel (3 Practical Examples)
- Add Average Line to Scatter Plot in Excel (3 Ways)
- How to Combine Two Scatter Plots in Excel (Step by Step Analysis)