Skip main navigation

Formatting Scatter charts

Once you have created a Scatter chart, you need to add formatting and a trendline could add useful information. Watch Paula demonstrate this now.
6.3
So I’m going to do a little bit more chart formatting to make this chart a little bit easier to read. So I’ve selected my axes and I am going to change some details of our axes. We can see that we don’t have anything with sales of less than 150 so I’m going to make our minimum sales a 150. And you can see that spreads the chart out a little bit more. But we also have no temperatures below 15 so I can change this axes and have a minimum value of 15 in here as well. And we can see now that really spreads out the chart a little bit.
44.6
Now we can select the all of the data points as well, so we want to select the data series. And in the data series, because these are markers and not lines, you need to go to markers. Then what you can do is you can change the color of the markers to suit you and you can put no line around the outside of them you can make them a little bit more transparent if you want to make them a little bit more transparent. Now, these dots now are extremely hard to read because I have them so transparent, but what I want to do is, I want to add a trend line.
85.5
And this trendline will show whether there is a correlation between the increase in temperature and we can see there is as it is sloping upwards between the increase in temperature and increase in sales. So to add our trend line, all we need to do is go to our chart elements, and in our chart elements,` we can then select our trend line. Now once we have added our trend line, if we select our trendline, we can format our trend area fairly quickly. I just clicked off that. So we have our format trendline.
125.1
Now we have the same trends available that you would have for a line chart and we are going to stick with our linear and I’m going to go in and format this a little bit more and I’m going to format the trend line so that it is the same color as the actual dots themselves. But I’m going to make the line itself a full line so you can see it an awful lot clearer in the actual diagram. Now in addition to this, what I’m also going to do is I am going to display the r-squared value on the chart. And this shows the correlation value between zero and one and the closer to one, the stronger the correlation.
172.5
So this is 0.85 so there’s a fairly strong correlation between the increase in sales and the increase in temperatures. So now we have this first chart created. I’m going to actually take a copy of this chart and I’m going to paste the chart down here and this time, we want to change this because we looked there at the ice cream sales but now what we’re going to chart is we’re actually going to chart the chocolate sales. So if you see over here at my data and just grabbing my data, and I’m pulling the data over. So then what we need to do is change the tempera- change the chart because we have ice cream sales on here.
219.6
So we would need our chocolate sales in our chart title instead of ice cream sales. And we don’t really need to make any other changes because we’d already formatted the first chart before we’d copied and pasted off. All we wanted to do was change the actual data series that we were using in the particular chart. [END]

Using the scatter charts you created in the last step, you can now add some formatting and a trendline. Explore all the formatting options to make sure you are familiar with what is available.

Comment below

What are your main takeaways from this video?

This article is from the free online

Introduction to Excel Charts for Data Visualisation

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education