How to Make a Scatter Plot in Excel with Two Sets of Data (in Easy Steps)

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 a scatter diagram) 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 axes plot numeric data across them.

The following image shows a simple scatter plot.

Scatter Chart Types

As, A scatter plot or an XY graph (also known as a scatter diagram) is a 2-D chart that shows a correlation between two variables. 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.

How to Make a Scatter Plot in Excel with Two Sets of Data: Easy Steps

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.

📌 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 representative. 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 or text to the 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.

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.

Download Sample Workbook

You can download the sample workbook from the following link.

Scatter Plot with Two Sets of Data.xlsx

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. 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.

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

Get FREE Advanced Excel Exercises with Solutions!