How to Make Scatter Plot in Excel (with Easy Steps)

Excel has a number of useful chart types that can be used to show analysis and plot data.  Putting X and Y values on an Excel chart to show how they relate to each other is a common task. This can be done with an Excel chart called a Scatter plot. In this article, we will learn how to make Scatter Plot in Excel.


Download Practice Workbook

You can download the practice workbook from here.


What Is Scatter Plot in Excel?

Scatter plots in Excel show how two variables are related. In a scatter plot, both of the axes show numbers, independent variable on the X-axis and dependent on the Y-axis. The chart puts together the values on the x and y-axis into a single point.


How to Set Up Data for a Scatter Plot

With the Chart templates in Excel, it is easy to make a Scatter plot in Excel. First, we need to put our source data in order. The independent variable should be in the left column. The variable that is dependent should be in the right column and on the y-axis.

In our example, we’ll show the amount spent on advertising and how many apples were sold in a certain month. So we set up the data this way:

How to Set Up Data for a Scatter Plot

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


Step-by-Step Procedures to Make a Scatter Plot in Excel

If the source data is set up correctly, making a scatter plot in Excel is as easy as following steps.

Steps:

  • First, choose three columns, including the column titles. In our case, the range is from B4 to D14.
  • Then, click the Charts group under the Insert tab, then click the Scatter chart icon and choose the template you want. Click the first thumbnail to add a simple scatter graph.

Step-by-Step Procedures to Make a Scatter Plot in Excel

  • As a result, the scatter plot will be added to your worksheet:

In the plot above, three columns are shown in two separate lines while the x-axis shows the number of months. We can also choose just two columns and get a graph to match. you will get a simple Scatter plot for now. Or, you can change some of your graph’s parts to make it look better according to your needs.


Types of Scatter Plot in Excel

In addition to the classic scatter plot shown above, there are also the following:

  • Scatter with Smooth Lines
  • Scatter with Smooth Lines and Markers
  • Scatter with Straight Lines
  • Scatter with Straight Lines and Markers

Scatter with Smooth Lines

When you have a lower number of data points, Scattering with Smooth Lines works best. For instance, here’s how the previous set of data can be shown using a scatter graph with smooth lines by following these steps.

Steps:

  • Initially, pick all columns, including the column titles. The range for us is from B4 to D14.
  • go to the Insert tab, click on Scatter chart, and select the following.

Scatter Plot with Smooth Lines in Excel

  • So, this will create a graph plot like the following.

Scatter with Smooth Lines

Read More: How to Add Line to Scatter Plot in Excel (3 Practical Examples)


Scatter with Smooth Lines and Markers

By selecting the Smooth Line with Markers option, we can have markers on our known values. To do so, we will follow these simple steps.

Steps:

  • At first, select the entire data along with column headings.
  • Then go to the Insert tab, and select the Scattering chart option.
  • Finally, select the Smooth Line with Markers option.

Scatter Plot with Smooth Lines and Markers in Excel

  • Selecting this option will give us the following type of plot.

Scatter with Smooth Lines and Markers

Read More: How to Add Average Line to Scatter Plot in Excel (3 Ways)


Scatter with Straight Lines

By selecting Scatter with Straight Lines, we can have straight lines from points to the next points. This type of plot is efficient for comparing two plots. To get such a plot, we will follow the steps below.

Steps:

  • In the beginning, the steps are the same as creating smooth lines plot. But while selecting the chart type, we will select Scatter with Straight Lines.

Scatter Plot with Straight Lines in Excel

  • Selecting this will create a graph plot like the following.


Scattering with Straight Lines and Markers

If we want to select Scattering with Straight Lines and Markers, we will have to select this option like the following.

Scattering Plot with Straight Lines and Markers in Excel

Choosing the option gives such a graph plot.

Scattering with Straight Lines and Markers


Customizing XY Scatter Plot in Excel

Excel, like other kinds of charts, allows you to adjust practically every aspect of a scatter graph. You may simply edit the chart’s title, add names to the axis, conceal the gridlines, customize the chart’s colors, and more after making a scatter plot in Excel.

Here we will discuss these topics specifically:

  • Adjust Axis Scale
  • Add Labels to Scatter Plot Data Points
  • Insert Trendline
  • Add Label to Axes

Read More: How to Combine Two Scatter Plots in Excel (Step by Step Analysis)


Adjust Axis Scale

You may wish to remove the additional white space if your data points are grouped at the top, bottom, right, or left of the graph. Excel allows us to change the scale of the Scatter Plot. While making a scatter plot In Excel follow these steps to reduce the distance between the initial data point and the vertical axis, as well as the final data point and the graph’s right edge.

Steps:

  • Firstly, right-click on the axis you want to adjust the scaling on. Then select the Format Axis option.

Adjust Axis Scale in Scatter Plot in Excel

  • Secondly, on the Format Axis panel, set the desired Minimum and Maximum limits as your need.

Adjust Axis Scale

You can also change the spacing between grid lines by changing the Major and Minor values. Here, by setting the Maximum and Minimum to 10 and 5, and decreasing the Major to 1, we will get the following Scattering with Straight Lines and Markers plot.


Add Labels to Data Points

When making a Scatter plot in Excel, you may want to name each point to make the graph easier to understand. To do so, follow the steps below.

Steps:

  • First, select the plot and click on the Chart Element button(the ‘+’ button).
  • Second, click on Data Labels. This will show the data values on those points. We can select the label placement by selecting the arrow mark in right.

Add Labels to Scatter Plot Data Points in Excel

  • By selecting More Options, we can go to the Format Data Labels option.
  • Then select Label Options. Here we can choose to show the Y-axis value and the label placement.

Add Labels to Scatter Plot Data Points

Here, in our Scatter with Straight Lines and Markers, let’s enable the X Value and disable the Y Value for Apple’s line and enable Data Label for Advertisement. We will get the following result.

Read More: How to Add Data Labels to Scatter Plot in Excel (2 Easy Ways)


Insert Trendline

Excel offers a feature called Trendline for Scatter plots. This feature enables us to understand the relationship between two different variables more precisely and can be used for Forecasting. Enabling Trendline will give us such results.

Insert Trendline


Add Title to Axes

For a better understanding of the Scatter plot in Excel, we can add titles to axes. We need to follow the steps to do so.

Steps:

  • At first, select the plot, and click on the Chart Element(the ‘+’ sign on the right) option.
  • Then select the Axis Titles option.
  • Then edit the axis name by selecting and writing into it.

Add Title to Axes in Scatter plot in Excel

  • Here after giving X and Y-axis titles, we get the following result.


Things to Remember

  • If your dependent column comes before your independent column and you can’t change this in a worksheet, you can swap the X and Y axes directly on a chart.
  • We must remember that the left column data will be on the x-axis and the right column data will be on the Y-axis in the plot.
  • Labels for points that are close to each other may overlap. To fix this, click on the labels, then click on the one that overlaps. A four-sided arrow will appear when you point the mouse at the label. After that, move the label.
  • For two different lines, all the properties are different. We need to change or modify properties for each of these lines separately.

Conclusion

Scatter Plotting is one of the most used features of Excel. In this article, we demonstrated how to easily plot a Scatter Plot. For any kind of data, we can apply these methods keeping in mind the conditions shown in the Things to Remember portion. Still, facing any problems regarding this topic? Feel free to ask through the comment box. Our team will be happy to answer your questions. For any type of Excel-based problem, visit our website Exceldemy.com.


Related Articles

Nasir Muhammad Munim

Nasir Muhammad Munim

I am Nasir Muhammad Munim from Dhaka, Bangladesh. I work as an Excel and VBA Content Developer for Exceldemy right now. Electrical and electronic engineering was my major at Islamic University of Technology, where I got my degree. I love creative works and learning new things. And I'm really into online games, especially first-person shooter games.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo