How to Make a Scatter Plot in Excel – Step by Step
This guide will provide a step by step run through on how to make a Scatter Plot in Excel.
Scatter Plots can be a really useful visual representation of our data, especially when we are hoping to find any trends and correlations between two separate variables. Luckily, making a scatter plot in Excel using two columns of data is incredibly easy.
In this guide we will cover:
- Our Dataset
- Step by Step instructions to make a Scatter Plot
- Additional Optional Steps
Let’s get started with our dataset.
Our Scatter Plot Dataset
For the purposes of this example we are going to just go with something super simple – years of experience and salaries of a small group of employees in a business – to see if there is a correlation between the two (meaning as years increase, so does salary).
Our dataset looks like the below:
As we can see, this is quite simple as it is simply two sets of numerical values.
Now that we have our data, let’s move on to the build itself.
Making a Scatter Plot in Excel – Step by Step
The first thing we need to do is select all of our data – this simply means highlighting everything (including the headers) from B2 down to C12 in our example dataset:
Next up, under the Insert menu up in the ribbon, in the Charts section, choose the Insert Scatter (X,Y) or Bubble Chart:
From here you notice there are quite a few options available, but the one we are looking to select is a simple Scatter Plot. If you move your mouse over all of them you will be presented with an overview of the types, and even scenarios where you would potentially need to use them:
From here just click on Scatter and the chart appears:
As we can see, this has provided us with a nice clean scatter plot, with the years experience on the X axis, and Salary on the y Axis.
This pretty much covers off the basics, but before we go any further let’s take a quick look at a couple of the other options, just to get an idea of why we chose the most basic option in this dataset.
If we had chosen something such as Scatter with Straight Lines and Markers we would essentially get the same data mapped out with the marker dots, but they would all be connected, and not just in a clean line chart kind of way that we would expect, as seen below:
This isn’t particularly intuitive for what we are trying to visualise, so in this case the simple scatter plot with markers only is definitely the better option!
You will also notice that outside of pure Scatter Plots, we also have Bubble Charts too sitting in this section. Let’s take a quick look at what would happen if we simply chose Bubble instead of Scatter:
Not too bad, but considering how close a lot of our data markers are it is a little bit hard to read in this case, even if we were to play around with the formatting and size of the chart it really feels like sticking to Scatter is the way to go here.
Now that we have the chart itself sorted, let’s take a look at some formatting.
Modifying our Scatter Plot
From here we have some additional optional steps we can take, mostly in the formatting space.
There are a few steps we will cover here:
- Changing the Title
- Adding Axis Labels
- Adding a Trendline
Firstly, to change our title simply click on the existing title and it turns into a text box – we are going to change this to something far more descriptive and useful – Years of Experience and Salary over Time
Next up is the labels. While it isn’t exactly hard to work out what each of the sets of numbers means given our title, it would look more readable and slick if we had axis labels – to add these we have a separate guide which covers the steps in depth that can be found here.
Finally, to make it even more clear whether or not there is a trend in our dataset, we can add a trend line. Again we have an in depth guide on how to do so here.
The final output of our new and formatted scatter plot in Excel looks as follows:
Much easier to interpret now!
One final point worth noting is that depending on the size of our data, we may want to remove any potential outliers before we visualise and add a trendline. This is where our guide on finding and removing outliers comes in handy which can be found here.
This sums up our step by step guide on how to make a scatter plot in Excel. For more handy guides on working with Excel, be sure to check out our Excel Tips page.
