How to Make a Scatter Plot in Excel – in Just 4 Clicks!

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

A scatter plot is one of the most useful visualization tools you can use in Excel.

Scatter charts are great for comparing values and showing their relationships among the values in the series.

We’ll talk about what a scatter plot is, its difference with a line chart, and when you should use one. 

Then, we’ll have a walkthrough on how to create one in just 4 clicks! 😊

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get started!

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

What is a scatter plot?

A scatter plot, also known as a scatter chart, XY graph/chart, or scatter diagram, is a chart where the relationship between two (2) sets of numeric data is shown.

It has 2 value axes — horizontal (x) and vertical (y) — that plot numeric data. Usually, the horizontal axis houses the independent variable while the dependent variable is on the vertical axis.

Scatter graphs display the combined data from both axes at the intersection of the axes.

Kasper Langmann, Co-founder of Spreadsheeto

Because of the chart’s similarity in form to a line graph, some users find it hard to choose which one to use.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Scatter Graph vs Line Graph

The line graph is one of the simplest graphs in Excel.

Like a scatter plot, it has a horizontal and vertical axis. But their main difference lies in how they plot the data on the horizontal axis.

Kasper Langmann, Co-founder of Spreadsheeto

Here’s why:

  • The horizontal axis on a scatter plot is a value axis. That means the point plotted on the chart is a combined value of both the values from horizontal and vertical axes.
  • On the other hand, a line graph only has one value axis — the vertical axis. 
  • What the horizontal axis on a line graph shows are usually time periods or labels of the variables being illustrated.

To demonstrate, let’s assume you’re in the e-commerce space and you’re now visualizing your previous year’s pageviews and sales.

data overview of scatter plot exercise: pageviews vs sales

Line graph:

Here’s why:

  • The horizontal axis on a scatter plot is a value axis. That means the point plotted on the chart is a combined value of both the values from horizontal and vertical axes.
  • On the other hand, a line graph only has one value axis — the vertical axis. 
  • What the horizontal axis on a line graph shows are usually time periods or labels of the variables being illustrated.

To demonstrate, let’s assume you’re in the e-commerce space and you’re now visualizing your previous year’s pageviews and sales.

a line graph of pageviews vs sales

As you can see, the line graph displays the data as two separate points distributed along the horizontal axis.

Scatter plot:

a scatter plot for pageviews vs sales

Because a scatter plot has both axes as value axes, it displays the data points on their intersection.

When to use a scatter plot?

Some of the charts and graphs in Excel have overlapping uses.

Here are a few points of when to use a scatter plot:

1. Showing the correlation of the variables.

The main purpose of a scatter plot is to show the correlation between the variables. If there’s no correlation, the points on the chart appear scattered.

However, if there’s a correlation, positive or negative, a diagonal arrangement (resembling a line) of the points can be observed. Like our example above, you can see a correlation where an increased in pageviews results in an increased in sales.

2. The variables being observed are numeric.

If the variables are numeric, a scatter plot is a good choice to visualize the data. If not, as in cases where the other variable is time (or periods of time), a line graph would do.

3. Spot trends in the variables.

Because scatter plots show the correlation between the variables, they’re also a good tool to spot trends.

If the variables on both axes increases, it denotes a positive trend or correlation. But if the variable in the vertical axis decrease, it’s negative.

4. Ability to scale the horizontal axis.

Because the horizontal axis is a value axis, scaling it is possible. You can also turn it into a logarithmic scale.

Before we discuss how to make scatter plots, let’s talk first the best way to format your data.

Kasper Langmann, Co-founder of Spreadsheeto

Formatting data for scatter plots

Formatting your data is straightforward.

What you have to pay attention to is the arrangement of the variable:

  • The independent variable should be on the left column so it can be plotted on the horizontal axis.
  • The dependent variable, which is affected by the independent variable, should be in the right column.

In our example, the ‘Pageviews’ is the independent variable while ‘Sales’ is dependent on the former.

pageviews labeled as independent and sales as dependent

But if you already had your data plotted and it’s not the ‘recommended’ arrangement, it’s fine. There’s an option in Excel where you can switch the two. We’ll discuss it later.

Kasper Langmann, Co-founder of Spreadsheeto

Now, let’s have a walkthrough on how you can create a scatter plot in Excel with just 4 clicks.

Making a scatter plot

Here’s how easy it is to make one:

how to make a scatter plot in excel in 4 clicks

For sure, you too would be able to make one in 5 seconds after this tutorial. 😊

Kasper Langmann, Co-founder of Spreadsheeto

Let’s break what just happened:

1st click: Select the two columns with the data.

In our example, it’s B3:C14.

highlight pageviews and sales

2nd click: Click ‘Insert’ from the tab list.

insert tab on microsoft excel 2019 tab list

3rd click: Click the ‘Scatter’ icon on the ‘Charts’ category on the Ribbon.

4th click: Select ‘Scatter’ under the ‘Scatter’ group from the dropdown options.

scatter icon on the scatter group on the ribbon

After you click that, your scatter plot will appear!

a plain looking scatter plot

Simple, right?

Looks too plain? Feeling nervous about the whitespaces and gaps? 

Don’t worry. You can edit scatter plots in Excel.

Kasper Langmann, Co-founder of Spreadsheeto

Editing scatter plots

There’s a lot of customization available to make your chart look better.

The easiest way to do this is by clicking ‘Chart Design’ on the tab list. (Don’t forget to click on your chart first or else this tab won’t show.)

chart design tab on tab list

Then, hover over the different chart styles available. This will give a preview of the style on your chart. 

Simply click which style you like.

preview of scatter plot chart styles

Did you see the gap of the first point on the left to both axes?

whitespace or gap of the first data point to both axes

To change that, right-click the horizontal axis and select ‘Format Axis’ from the dropdown. A window will then open on the right-hand side.

right clicking the horizontal axis and selecting format axis from the dropdown

Here, you can see various options to format the axis.

To minimize the gap, change the ‘Bounds’ of the variable.

highlighted the minimum bound under axis options

Let’s set the minimum to 300. 

(After hitting ‘Enter’, the bounds are resized but the maximum grows to 1700. Feel free to change the maximum back to 1600.)

minimum bound of y axis is changed to 300 while the maximum to 1700

To change the bounds on the vertical axis, just click on a value on the axis directly. The window will automatically show the options of the chosen axis.

Change the minimum bound to 20.

minimum bound of x axis is being changed to 20

In addition, you can add (or remove) certain elements on the chart:

  • Axes
  • Axis Titles
  • Chart Title
  • Data Labels
  • Error Bars
  • Gridlines
  • Legend
  • Trendline

To access them, click the chart then click the plus (+) symbol on the upper right side of the chart.

plus symbol as chart elements on the upper right side of the chart

If you’re not sure how an element changes the chart, just hover your mouse over it and you’ll see a preview.

A trendline reveals the relationship between the two variables. Excel draws the line as close to all data points as possible.

Kasper Langmann, Co-founder of Spreadsheeto

Chart and Axis Titles

If you like to name your chart and the axes, you don’t have to retype them.

To save time: 

  • Click on the element (‘Chart Title’ or ‘Axis Title’)
  • Type an equal (=) sign 
  • Click the cell with the title 
  • Press ‘Enter’

Switching the axes

If the arrangement of your data isn’t like the ‘recommended’, you don’t have to swap the columns themselves.

Excel allows the switching of axes in the chart.

To start, right-click on either of the axes and select ‘Select Data’ from the dropdown.

select data option after right-clicking an axis

This will open the ‘Select Data Source’ window. 

Now, click ‘Edit’.

edit option on select data source window

The goal here would be to swap the values inside the ‘Series X values’ and the ‘Series Y values’.

The easiest way to do this (without using a notepad) is to: 

  • Copy one (‘Series Y values’) of them to the ‘Series name’. 
  • Then, copy the other one (‘Series X values’) to the other’s (‘Series Y values’) space. 
  • Cut (Ctrl + X) the value in the ‘Series name’ and paste it on the first one (‘Series X values’).
how to switch source data faster

Voila! You now have the appropriate variables on each of the axes.

Wrapping things up…

Scatter plots excel at comparing two variables and showing their correlation with each other. With Excel, you can create one in just a few clicks.

The best thing about creating a scatter plot in Excel is you can edit and format your chart to present the data effectively.

Kasper Langmann, Co-founder of Spreadsheeto
2019-09-02T12:54:07+00:00