In this screen cast, I'm going to show you how to make Scatter plots in Excel. These are good ways to visualize data. In a scatter plot, we have an independent variable. A lot of times we call that x, that goes on this horizontal axis, and then we have the dependent variable, oftentimes we call this y, that goes on the vertical axis. The dependent variable is some sort of property or variable that depends upon the independent variable. Scatter plots are generally used for time series data. So a lot of times, the independent variable is time. We use this for continuous non-categorical data. You wouldn't want to use this for situations in which the independent variable is a categorical data. Scatter plots show correlations between two variables. The important thing about scatter plots is that you have paired data. So in this example up here, each one of these markers represents a pair of independent variable value and dependent variable value. So the data is paired. An example of this would be high temperature as a function of the date. Each of those dates has a high temperature and so we have paired data. We can add multiple series on a single plot. This red line here is a series. This is the high temperature but maybe we wanted to add in the low temperature. In order to add in multiple series onto the same plot, the x and y values, have to represent the same variable. They can be different, for example, high temperature and low temperature but they are both temperatures. The multiple series are distinguished from each other using a legend. An example of this, as I just alluded to, would be now, instead of having the y-axis labeled high temperature is just labeled temperature and now we can put two series in here, one for the high temperature and one for the low temperature, as a function of the date. Sometimes you want to add in another set of data. So maybe we have precipitation as a function of date. Precipitation is not the same in nature, is not the same as a temperature. In that case, we will put precipitation on a secondary axis. The temperature scale would have its different values here, maybe ranging from 20 degrees up to 70 degrees Fahrenheit or so in precipitation then, we would have different markers here, so a different scale here on the secondary axis corresponding to precipitation. So now I'm going to show you how we can create these scatter plots in Excel. So I've got a file here called Dairy products. As a function of the year, we have the per capita consumption of American cheese, ice cream, yogurt and butter. We also have in a column over here, completely unrelated, we have still, as a function of time, we have the milk per cow that's produced in the United States in average in pounds per year. The first thing I'm going to do is, I'm just going to create a simple scatter plot for American cheese consumption as a function of the date, the year. So I can select these two, this first row, Control Shift Down to select the entire series and then we go up here to Insert, then we go over here to the charts group. There's all sorts of different options. A lot of times, the recommended charts will show you a recommended one. What I like to do is, I like to just build it from scratch and I know that this is going to be a Scatter with smooth lines and markers. So I'm just going to go ahead and click that. There are other options that you can play around with. There is our charts. I'm going to go ahead and move this up to the top. I like to remove the title because a lot of times that information is redundant because it's already on the axis or you have it in a figure caption, if you're putting this in a paper or a presentation, that's our series. We can always left-click on the chart, up here, we can add chart elements with this plus sign. Alternatively, you can go up here to the chart design and that tab is up, if we selected that chart, and we can go over here to Add Chart Element. You can select from a bunch of different things. What I'm going to do first is I'm going to add in some titles. I like to do that with this plus sign over here. So Plus Axis Titles and now we've got our axis titles. To change the axis titles, you can just click on one of those and we can start typing this in. You notice that when I typed in, it's up here in the formula bar, I just typed in year but it's not yet showing down here in the axis title, until I press Enter. So that's how we can change that to year. I'm going to change the y-axis title to consumption, inbound per person per year and I press Enter and we've labeled that axis. So this would be a nice scatter plot. You notice that we're only using the upper half of this scatter plot. You can change the scale here. Maybe we want this to start at eight instead of zero, you can right-click on that and then go to Format axis and this is where you can change a lot of the options with the axis. I'm going to put in eight and press Enter, and it's re-scaled that axis. You can change the series. You can right-click on here and we go to Select Data, and it brings up this Select Data Source. This is where we can edit this and we can name the series. So maybe this first one I wanted to name, American cheese. That's how we can make a name for it. But this is where you can change the x and y values that that series corresponds to. What I'm now going to do is add a couple of series. We're going to add in ice cream, yogurt and butter. Because ice cream, yogurt and butter in the data here, also is in per capita consumption in pounds per person per year, I can use it on the same primary axis. The first way to add a series to a scatter plot is you can right-click. Like I mentioned before, you can go to select data. You can also select data up here in the Chart Design tab. I'm going to select data. So I'm going to add a series here. This is going to be named ice cream. The x values here, we can select this little arrow here and I'm going to go to the year. So I'm going to select 1980, Control Shift Down. We can select this arrow again. Now the y values, when you have this little one here, I don't know why this is the case, but it's always been like this as long as I can remember. You have to delete that and then you click the arrow and we're going to add in our ice cream. So that's cell C4, Control Shift Down and then I can drop down that arrow again and I can click Okay and I can click Okay again. You see that it has added the ice cream to our plot. So we have two different series. Now the user, whoever is looking at this plot, isn't going to know which of those lines corresponds to American cheese or ice cream or even what they refer to at all. They're not going to know that either of those refers to cheese and ice cream. So it's nice to add in a legend. So you can go up here to the plus sign. Alternatively, you can go up here to the Add Chart Element. So let's select it from up here, the legend. Let's just put this to the right. So there's our legend. We can format this. A lot of times, I like to move the legend down and I like to expand the chart, so it takes up the entire figure and I like to add in, I like to right-click here and format this legend a little. If you go to this paint bucket, you can select the fill. I like to put in a solid fill of white. You can mouse around. I like to add in a border, so I'm going to select the solid line and I like that to be black. I would encourage you to mess around and mouse around with these different formatting options, so you know what's available. But I like to put this in the lower right-hand corner or upper right-hand corner. But there is our nice scatter plot. Very self-explanatory. The user can look at consumption of American cheese and ice cream as a function of year, they can distinguish between the two lines and it's nice and clean.
