How To Create A Scatter Plot In Excel With Labels

When you need to visualize the relationship between two sets of quantitative data, Microsoft Excel makes it possible for you to create an X-Y scatter graph.

For regression analysis, scatter plot graphs are the most important data visualization tool. However, you may be wondering how to make a scatter plot in Excel. Keep reading this data-based article to learn how.

Scatter Plot Excel—When You Should Use It

In Microsoft Excel, you may confuse whether an X-Y graph is a scatter plot or a line graph. Both are similar except for the data representation along the horizontal (X) axis.

A scatter chart consists of two value axes for quantitative data visualization. The horizontal (X) axis represents one set of numerical data, and the vertical (Y) axis indicates another data set.

But, the Excel line graph visualizes all category data on the horizontal (X) axis and numerical values on the vertical (Y) axis.

In Excel, you can create a scatter plot graph to visualize and compare numeric values obtained from scientific and statistical analyses. In the following scenarios, you should use a scatter plot instead of a line graph:

To analyze if there is any correlation between two sets of quantifiable values. The appearance of the X and Y chart will be quite similar to a diagonal arrangement.
To explore positive or negative trends in the variables.
To scale up the horizontal (X) axis.
To visualize outliers, clusters, non-linear trends, and linear trends in a large set of data.
To compare large numbers of time-independent data points.

How to Make a Scatter Plot in Excel

Here are the steps to create a scatter plot using the X-Y graph template in Microsoft Excel. You can use the following data sets as an example to create a scatter plot.

1. To start with, format the data sets to put the independent variables in the left side column and dependent variables in the right side column. In the above data sets, advertising budgets are independent variables, and items sold are dependent variables.

2. You need to select two columns in Microsoft Excel with numeric data. Make sure to include the column headers too. In this case, the range is B1:C13.

3. Now, click on the Insert tab on the Ribbon and then select the scatter plot template you like from the Charts section. For this tutorial, it is the first thumbnail which is the classic scatter chart.

4. The classic X-Y graph scatter chart will show up in the Microsoft Excel worksheet. It is the most simple form of scatter plot graph. You can also customize to visualize the correlation clearly and professionally.

Miscellaneous Optimization for Scatter Plot Graph Visualization

Excel allows you to customize the scatter plot in a number of ways. Here are some of the possible modifications you can make:

Scatter Chart Types

The X-Y scatter plot is the most common scatter plot type. Others include:

Scatter with Smooth Lines and Markers.
Scatter with Smooth Lines.
Scatter with Straight Lines and Markers.
Scatter with Straight Lines.
Bubble X-Y scatter.
3-D Bubble X-Y scatter.

Customizing X-Y Graph Scatter Chart

When you create a scatter plot in Microsoft Excel, you have the freedom to customize almost every element of it. You can modify sections like axis titles, chart titles, chart colors, legends, and even hide the gridlines.

If you want to reduce the plot area, follow these steps:

Double-click on the horizontal (X) or vertical (Y) axis to open Format Axis.
Under the Axis Options menu, set Minimum and Maximum Bounds as per the data sets.
The scatter plot graph will resize accordingly.

If you want to remove the gridlines, perform these steps:

Double-click on any of the horizontal gridlines within the X-Y graph plot area.
From the sidebar Format Major Gridlines menu, select No line.
Now, click on any of the remaining vertical gridlines and select No line.
Gridlines will disappear from the scatter chart.

Microsoft Excel also offers professional scatter chart templates. This is how you can check those out:

Double-click on the blank chart area.
On the Ribbon, look for Quick Layout within the Chart Layouts section.
Click on Quick Layout, and you will see 11 preset layouts to create a scatter plot.
Hover the pointer on each of them to know the features and choose the one that suits your data sets.

Add a professional look to your scatter graph by following these steps:

Click on any blank space of the chart to open Chart Tools on the Ribbon.
Under the Design tab, you will see 12 styles for the X and Y chart.
Select any to instantly transform the classic scatter plot graph into a stylish one.

Add Labels to Scatter Plot Excel Data Points

You can label the data points in the X and Y chart in Microsoft Excel by following these steps:

Click on any blank space of the chart and then select the Chart Elements (looks like a plus icon).
Then select the Data Labels and click on the black arrow to open More Options.
Now, click on More Options to open Label Options.
Click on Select Range to define a shorter range from the data sets.
Points will now show labels from column A2:A6.
For a clear visualization of a label, drag the labels away as necessary.

Add a Trendline and Equation in the Scatter Plot Graph

You can add a line of best fit or a Trendline in your scatter chart to visualize the relationship between variables.

To add the Trendline, click on any blank space within the scatter graph.
Chart Layouts section will appear on the Ribbon.
Now click on Add Chart Element to open the drop-down menu.
From that menu, click on Trendline and then choose the trendline style that fits the data sets.

To visualize the mathematical relationship between the data variables, activate the equation display on the scatter plot graph.

Double-click on the Trendline.
Format Trendline sidebar will open.
Within this sidebar, click on Trendline Options.
Now, check the box for Display Equation on Chart.

Scatter Graph and Variable Correlation

X and Y chart scatter graph can visualize three types of correlation between the variables in the data sets for meaningful data presentation. These correlations are as follows:

Negative Correlation: In a negative correlation, the value of one variable increases while the other decreases.
Positive Correlation: A strong example of a positive correlation is when vertical (Y) axis variables increase, the horizontal (X) axis variables increase too.
No Correlation: There will be no correlation if dots are scattered around the whole scatter chart area.

Impress the Audience by Creating Scatter Plot in Excel

Microsoft Excel is a robust application that allows you to create the next-generation scatter plot graph. Having learned how to create a scatterplot in Excel, you can also create smart charts in the program that update automatically.

How to Create Self-Updating Microsoft Excel Charts in 3 Easy Steps

Self-updating Excel charts are huge timesavers. Use these steps to add new data and watch them automatically show up in a chart.