How to create a scatter plot in Excel with 3 variables

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 tool

How to create a scatter plot in Excel with 3 variables

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.

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

Then, well have a walkthrough on how to create one in just 4 clicks!

Kasper Langmann, Co-founder of Spreadsheeto

Lets get started!

Table of Content

  • Chapter 1:What is a scatter plot?
  • Chapter 2: Grab your free exercise file here!
  • Chapter 3: Scatter Graph vs Line Graph

Chapter 4:When to use a scatter plot?

Chapter 5:Formatting data for scatter plots

Chapter 6:Making a scatter plot

  • Chapter 7:Editing scatter plots
  • Chapter 8:Switching the axes

Chapter 9: Wrapping things up

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 charts 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.

Ive included one for you (for free).

Download it right below!

Download the FREE 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

Heres 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, lets assume youre in the e-commerce space and youre now visualizing your previous years pageviews and sales.

Line graph:

Heres 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, lets assume youre in the e-commerce space and youre now visualizing your previous years 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 theres no correlation, the points on the chart appear scattered.

However, if theres 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, theyre 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, its 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, lets 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.

But if you already had your data plotted and its not the recommended arrangement, its fine. Theres an option in Excel where you can switch the two. Well discuss it later.

Kasper Langmann, Co-founder of Spreadsheeto

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

Making a scatter plot

Heres how easy it is to make one:

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

Kasper Langmann, Co-founder of Spreadsheeto

Lets break what just happened:

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

In our example, its 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?

Dont worry. You can edit scatter plots in Excel.

Kasper Langmann, Co-founder of Spreadsheeto

Editing scatter plots

Theres 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. (Dont forget to click on your chart first or else this tab wont 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.

Lets 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 youre not sure how an element changes the chart, just hover your mouse over it and youll 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 dont 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 isnt like the recommended, you dont 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 others (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.

Kasper Langmann, Co-founder of Spreadsheeto

Video liên quan