How to Make a Scatter Plot in Excel

A scatter plot is mainly used for representing the relationship or correlation between two numerical variables. It is also known as XY graph, scatter chart, scattergram. It is one of the most interesting and useful data visualization tools in Excel.

Scatter plots work best when comparing values and showing how one variable affects the other. For instance, you can use a scatter chart to find out whether investments are associated with profits, whether cigarette smoking is related to cancer, or whether more studying is associated with high scores, etc.

A scatter chart plots numerical data on two axes – independent variable on the horizontal axis and dependent variable on the vertical axis. In this tutorial, we will give you step-by-step instructions on how to create a scatter plot/chart in Excel.

Creating a Scatter Plot

The first step in creating a chart is creating a data set (table). As we already mentioned, a scatter chart represents the relationship between quantitative variables. Therefore, you need to add two sets of numerical data into two separate columns in Excel.

The independent variable must be entered on the left column of the table so it can be plotted on the X-axis.
The dependent variable, which is affected by the independent variable, must be entered in the right column of the table so it can be arranged on Y-axis.

Example:

A local cool drinks shop monitors how many cool drinks they sell against the afternoon temperature on that day. Here are their sales figures for the last 13 days.

First, select the two columns with data as shown in above picture.

Next, go to the ‘Insert’ tab and click on the ‘Scatter’ icon from the ‘Charts’ group on the Ribbon. Select your chart type from the drop-down options.

You can select whatever chart type you prefer (Scatter, Scatter with Smooth Lines and Markers, Scatter with Smooth Lines, Scatter with Straight Lines and Markers, Scatter with Straight Lines, Bubble, or 3-D Bubble).

We are choosing basic ‘Scatter chart’ for our data.

Formatting Axis on Scatter Chart

As you can see there’s a gap of the first point on the left of both axes. You can change that in the ‘Format axis’ pane. To do that right-click on the horizontal axis and click ‘Format Axis’.

A pane will open to the right side of the Excel. Here, you can format your axis using various options. To reduce the gap, change the ‘Bounds’ value. Let’s set the minimum to ’12’. And to reduce the gap on the vertical axis, just click on the axis directly. The WIndow will automatically change to the chosen axis and shows its options.

Change the minimum value of the ‘Bounds’ of the vertical axis. Let’s set it to ‘100’.

As you can see the gap is reduced and the scatter looks better now.

Adding Elements on Scatter Chart

You can add or remove specific elements on the chart (such as Axes, Axis Titles, Data Labels, Error Bars, Gridlines, Legend, Trendline) using the ‘+’ sign floating button on the top right corner of the chart or from the ‘Design’ tab. Let’s add titles to the axes.

Click the ‘+’ floating button, expand ‘Axis Titles’ and check the ‘Primary Horizontal’ and ‘Primary Vertical’ boxes to add titles to both axes.

We have added the titles ‘Temperature’ to X-axis and ‘Sales’ to Y-axis.

Adding Trendline and Equation to Scatter Chart

Adding a trendline to the chart helps us understand the data better. To add a trendline, click the plus (+) symbol on the upper right side of the chart. Then, click ‘Trendline’ and choose the desired trendline option. In this case, we are choosing a ‘Linear’ trendline into the chart.

To add an equation for the trendline that shows the relationship between the two variables, click ‘More Options’.

A ‘Format Trendline’ pane will open on the right side of your Excel. You can also access this panel by right-clicking on the trendline and selecting the ‘Format Trendline’ option. Then, check the ‘Display Equation on chart’ option.

A trendline and its equation are added to the chart and now the scatter plot looks like:

If you’re wondering how an element changes the chart, just move your mouse over it and you will get a preview.

Switching Axes on a Scatter Plot

A scatter chart usually shows the independent variable on the x-axis and the dependent variable on the y-axis, if you don’t like that, you can always switch the axis on the chart.

To do this, right-click on either of the axes and ‘Select Data’ from the drop-down.

In the ‘Select Data Source’ dialog window, click the ‘Edit’ button.

The ‘Edit Series’ pop-up window will appear. All we need to do here is swap the values inside the ‘Series X values’ and the ‘Series Y values’.

Then, click ‘OK’ twice to close both dialog boxes.

As the result, variables on each of the axes will switch places.

Formatting Scatter Chart

After you added elements, you can format each and every part of the chart. You change colors, sizes, effects, text format, chart style, etc. We can even change the colors of data points (dots) on the chart. Let’s see how we can do that.

Right click on the dots and select ‘Format Data Series’ option from the context menu.

In the ‘Format Data Series’ side window, select ‘Fill & Line’ tap under ‘Series Options’, and click the ‘Maker’ option. Then, check the ‘Vary colors by point’ checkbox under the ‘Fill’ section.

Checking this box will provide different color to each data point or dot.

To increase the size of the points, under the ‘Marker’ section, expand ‘Marker Options’ and then select ‘Built-in’ and increase the size as shown below.