Masum Mahdy
In this article, I will show you how to make a scatter plot in Excel with two sets of data. A scatter plot or an XY graph (also known as scatter diagrams) is a 2-D chart that shows a correlation between two variables. A closer allocation of data points along the trendline indicates a higher correlation between them. Like all other graphs, scatter plots also have horizontal and vertical axes. In this category of diagrams, both the axes plot numeric data across them.
The following image shows a simple scatter plot.
Download Sample Workbook
You can download the sample workbook from the following link.
Scatter Chart Types
Microsoft Excel provides 5 types of scatter plots. They are-
- Scatter
- Scatter with Smooth Lines and Markers
- Scatter with Smooth Lines
- Scatter with Straight Lines and Markers
- Scatter with Straight Lines
Types of Variable Correlation in Scatter Graph
There are 3 types of possible correlations between variables in scatter diagrams. They are as follows.
- Positive Correlation: The variable along the Y-axis increases with the increase of the variable on the X-axis.
- Negative Correlation: Variable along one axis decreases with an increase of the other variable.
- No Correlation: The data points are so scattered, and it seems that the variables are not relatable.
Read More: How to Make a Correlation Scatter Plot in Excel (2 Quick Methods)
Steps to Make a Scatter Plot in Excel with Two Sets of Data
Now we will learn how to make an Excel scatter diagram with two sets of data. The following data have price info of crude oil and gas in the U.S.A. for the last 12 months. We will try to find the correlation between them with the help of this diagram.
Follow the steps below to do that.
📌 Step 1: Arrange Data Properly
The first task here is to arrange the data properly. Remember that a scatter plot displays two interlinked numeric variables. Therefore, you have to enter the two sets of numeric data in two separate columns. The variables are of two types. Place the independent variable in the left column, and the dependent variable in the right column.
📌 Step 2: Select Data
Next, you have to select the two columns including their headers. Avoid selecting any column other than these.
Read More: Use Scatter Chart in Excel to Find Relationships between Two Data Series
📌 Step 3: Create Scatter Plot
- Now, Go to the Insert tab.
- From the Chats group, click on the Insert Scatter (X, Y) or Bubble Chart button.
- Now, select a suitable scatter plot template. In this case, we have chosen the first one.
- If you have fewer data points, you can choose other scatter plot types too.
Look at the following image which represents the scatter plot with two sets of data.
📌 Step 4: Customize the Graph
Seeing the above scatter graph, you could feel like it would be nice if we could do some formatting to the graph to make it more representable. So, in this step, we will show some basic formatting we can do to such graphs.
Edit Chart Title:
Double click on the chart title and type a title as you wish (e.g., Crude Oil vs Retail Gas Price).
Format Vertical/Horizontal Axis:
To remove the white space around the data points, we can format the X and Y axes changing the starting point of the graph. For example, in this graph, 68.52 is the first value along the X-axis. But the graph starts from zero point. Similar things go to Y-axis. So, we will do the following formatting for them with the steps below.
- Click on the chart area and go to the Format tab.
- Click on X-axis. The following side window will appear on the right.
- From the Format Axis window, click on the Axis Options button and then click on the Axis Options drop-down menu.
- Now, input 60 in the Minimum box and 120 in the Maximum box located in the Bounds section. Then press ENTER.
The graph looks better now. 👇
Similarly, we can format the Y-axis. Finally, the graph will have the following look.
Add Data Labels:
You can add data labels on your scatter plot in the following way.
- Click on the scatter plot and then click on the Chart Elements button.
- Then click on the Data Labels drop-down >> More Options.
In this stage, the graph will achieve the following look.
💬 Note:
Showing data labels is suitable for scatter plots having fewer data points. If your graph has many data points (like us), the data labels will make the points congested and less presentable.
You can add more formatting with the options in the following window.
Add Trendline and Equation:
You can add a trendline for the data points and format it in the following way.
- Click on the chart area and then on the Chart Elements button.
- Mark the Trendline box.
- From the Trendline drop-down, select More Options to add formatting to the trendline.
To add a Correlation Equation to the graph, go to the Format Trendline window as described above and mark the Display Equation on the chart box from the Trendline Options section.
Select a Suitable Chart Style
You can also change the chart style of your scatter plot in the following way.
- Click anywhere in the chart area.
- Then go to the Chart Design tab.
- Go to the Chart Styles group.
- Select a suitable chart style from the available formats.
After following all these, the scatter plot will look like the following image.
Read More: How to Add Multiple Series Labels in Scatter Plot in Excel
Notes
- The graph shows that the crude oil price and the retail gas price have a positive correlation between them (the slope is positive). You can quantify their correlation using the CORREL function. Results closer to 1 mean a tighter link between them.
- If you hide the columns that contain the numeric data, the scatter plot will be temporarily out of the site if you unhide the columns again.
Conclusion
In this article, we have discussed how to make a scatter plot in Excel with two sets of data and shown how to format the graph too. If you have found this write-up useful, please let us know in the comment box. Also, don’t hesitate to ask if you have any queries. For more such articles, please visit our blog ExcelDemy.
