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

Get FREE Advanced Excel Exercises with Solutions!

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.

Scatter Plot Example


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

Different types of scatter plots


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.

Showing positive correlation with scatter plot

  • Negative Correlation: Variable along one axis decreases with an increase of the other variable.

Showing negative correlation with scatter plot

  • No Correlation: The data points are so scattered, and it seems that the variables are not relatable.

Scattered data with no correlation

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.

Make scatter plot with two sets of data: Sample dataset

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.

Arrange two sets of data to make a scatter plot


📌 Step 2: Select Data

Next, you have to select the two columns including their headers. Avoid selecting any column other than these.

Select two sets of data

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.

Create scatter plot with two sets of data

Look at the following image which represents the scatter plot with two sets of data.

Scatter plot


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

Scatter plot vertical and horizontal axis formatting

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

scatter plot in excel with two sets of data

Similarly, we can format the Y-axis. Finally, the graph will have the following look.

scatter plot in excel with two sets of data

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.

Make a scatter plot in Excel with two sets of data

After following all these, the scatter plot will look like the following image.

Scatter plot in Excel with two sets of data

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.


Related Articles

Masum Mahdy

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo