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! 😊
Let’s get started!
Table of Content
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.
Because of the chart’s similarity in form to a line graph, some users find it hard to choose which one to use.
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.
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.
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.
As you can see, the line graph displays the data as two separate points distributed along the horizontal axis.
Scatter plot:
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.
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.
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.
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:
For sure, you too would be able to make one in 5 seconds after this tutorial. 😊
Let’s break what just happened:
1st click: Select the two columns with the data.
In our example, it’s B3:C14.
2nd click: Click ‘Insert’ from the 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.
After you click that, your scatter plot will appear!
Simple, right?
Looks too plain? Feeling nervous about the whitespaces and gaps?
Don’t worry. You can edit scatter plots in Excel.
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.)
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.
Did you see the gap of the first point on the left 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.
Here, you can see various options to format the axis.
To minimize the gap, change the ‘Bounds’ of the variable.
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.)
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.
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.
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.
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.
This will open the ‘Select Data Source’ window.
Now, click ‘Edit’.
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’).
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.