How To Create A Scatter Plot In Excel?

Let's make a few scatterplots with Excel and what we're also going to do is. We're going to put some trend lines in there and trend lines. Sometimes help us visualize the relationship that might be in a scatterplot. Now we make scatter plots using two How To Create A Scatter Plot In Excel quantitative variables one on the x-axis and one on the y-axis. Let's just pick the first two variables here that are quantitative in this data set. That has a list of 93 different cars and the data here is really old from the Year1993. But it's data from Consumer Reports that has the minimum price. Horsepower's average price paid highway miles per gallon and some other things. So let’s look at a few of these scatterplots to see is there a relationship between some of these variables. So let’s look at the minimum price and the horsepower just because these are right next to each other. What Excel will normally do is put the variable that ‘son the left-hand side on the x-axis of the scatterplot.

The variable on the right-hand side on the y-axis and is the minimum price in thousands of dollars. So let’s select these two variables and then let’s go to insert and over here on the charts area. There's a little one that has dots and it says insert How To Create A Scatter Plot In Excel Scatter Chart. Now let's just pick the basic kind of chart here and there's our scatterplot. Now what I wish Excel would do is label the x and the y axis automatically. Now we can kind of tell and also I told you that the general rule. That Excel uses is the variable on the left goes on the x-axis and that's what it’s done variable on the right is on the y-axis. But still, when you make a scatterplot you want to make
sure to label these. So let's right-click is one way to do it another way to do it is that. Sometimes is a little quicker is going to click on the design tab here and go to add chart element and X's titles. We want to add a primary vertical and a primary horizontal axis.

So the vertical axis is the y axis let’s click here and delete the words axis title and tell people. What this is horsepower and we probably want to move this graph over a little bit. So that we can actually read the title their horsepower and then let's do the same thing. Let's add an x-axis here, so add chart element axis title primary horizontal. Click here to delete that How To Create A Scatter Plot In Excel text and tell people that this is the minimum price in thousands. So now let’s look and see what we have and we probably want to give a better title here telling people. That this is a scatterplot and you know maybe a more important title than that. But let’s just see what we have here it looks generally like we have an upward sloping relationship where. Which we would call an appositive relationship or a direct relationship. Where more expensive cars, in general, seem to have higher horsepower.

Now if you ever have a scatterplot where it's a little hard to judge or it's hard to see. What that relationship looks like then what we can do is add a trend line. Some people will also call this a regression line. So if you want to add adrenaline right-click on one of the dots in your scatterplot and go to add a trend line. You see a lot of options here on the How To Create A Scatter Plot In Excel right do you want to add a curved. You want to add a straight line in general you want to stick with a straight line unless. You actually see a curved relationship and then you can try that. But let's just add a straight-line linear trend line here and that looks fine to me. So it added it as a dotted line we can change the format of that. If we want but what this does is roughly give us a line of best fit that. If we were to use a straight line to explain this relationship between price and horsepower. That's what that line would look like and what we see here is that the relationship is a pretty good one. But the relationship looks like it's a much closer relationship meaning the points are much more tightly grouped.

Where we can see that as the price goes up the horsepower goes up at the bottom. But as we get to higher and higher horsepower vehicles this relationship becomes less defined less clear. There’s a lot more spread above and below the line let's look at How To Create A Scatter Plot In Excel another example here to see. What happens now let's this is an appositive relationship. Let's see if we can find a couple of variables that have a negative relationship and let me cut this. I'm going to move it to a separate sheet. So let's go back to the data something that probably has a negative relationship would be perhaps horsepower and miles per gallon. As you get a bigger engine the miles per gallon you get probably goes down. We get out we could look at horsepower or we could look at engine size and probably get the same relationship. Let's use horsepower so I'm going to select the horsepower variable and now I'm on a Windows computer.

If I want to select another variable that's not adjacent select one variable. Then let's hold down the control key and then select the second variable and then release the control key. That way we can select two variables that are not right next to each other. We do the same thing How To Create A Scatter Plot In Excel in sir go to the scatterplot and again we probably want to label these axes. But for now, we can kind of see what's going on this is horsepower on the x-axis and this is miles per gallon on the y-axis and in this case. We see a negative relationship in general as the horsepower gets higher. We see that these vehicles get lower miles per gallon now this one kind of looks like a curve though. Let's insert a trend line and let’s start with a straight line and just see what that looks like. You see this line there's a lot of points that are way above the line here. But not too many below it on the left most of the points are above the trend line oral the points are above the trend line on the right.

So that suggests that maybe curves relationship might be something we want to look into here. So you can say right-click add a trend line. just pick one of these other curve shapes exponential logarithmic power etc. we can see. If we can get one and you see that it's inserting it as we go. So that we can get an idea of what it will look like a polynomial might do How To Create A Scatter Plot In Excel job a power might do the job. Maybe we'll go with power let's delete the straight-line there. So that we don't get confused between the two and we can see that. That curve might give us an idea of what that relationship looks like just a little bit better. Then the straight line dip now let me do one more scatterplot and I’m going to do try to do one. Where we’re not going to see a very clear relationship when we make it. So let me move this just in case we want to look back at it at a future date or a future time. Let's make a scatterplot between say the minimum price and then again. I'm going to hold down the control here and let's use the turn radius.

Maybe those have a relationship and maybe they don't let's see insert scatter plot and boy it sure kind of sure hard to see. If anything's going on there now although when people when professionals make graphs. Generally, we do have a preference that the origin is in the graph unless. There's a really good reason not to sometimes that can distort the way How To Create A Scatter Plot In Excel the graph looks here. Since most of this data the so here the price is on the x-axis the U-turn radius is on the y axis. There's very little going on except for between 30 and 50. Maybe we do want to zoom in on the y-axis and get rid of this empty space. Let’s just see if that changes our perception of what's going on here. So I’m right-clicking I went to the format axis let’s change the minimum to 30 and just see.

If that reveals any kind of pattern that we weren't seeing. Before we do see that others are a large cluster over here that seems to have a positive relationship. But then we have all these other points over here on the right that’s kind of lead us to How-To Create A Scatter Plot In Excel believes. Maybe there isn't so much of a positive relationship anymore again. If we want to get an idea of what Excel thinks by doing some calculations. We can insert a trend line and the trend line seems to tell us. That the majority of the data is suggesting an appositive relationship. But it's very it’s not what I would call a very clear or very strong relationship here. Between the price and the U-turn, in general, we might expect more expense of cars to maybe be a little larger. That larger car might require a larger distance to make a U-turn. But it’s not a very clear result here so here we see three different graphs. That we’ve made using scatter plots that give us sort of three somewhat different results. A fairly clear positive relationship but a somewhat curved negative relationship. Then a very unclear kind of muddy murky positive relationship between price and U-turn. So, I hope you've learned something and found this a little bit interesting about how to make scatterplots.