Skip to content
Skip to main content
header search
ahihi OpenLearn
OpenLearn
Menu
sticky search

About this free course

Become an OU student

Download this course

Share this free course

Course content Course content
Data analysis: visualisations in Excel
Data analysis: visualisations in Excel

Start this free course now. Just create an account and sign in. Enrol and complete the course for a free statement of participation or digital badge if available.

More free courses

3.2 Scatter diagrams

A scatter diagram is another way to visualise a quantitative bivariate data set. This is a two-dimensional diagram or graph with one variable on the x-axis (the independent variable) and the other variable on the y-axis (the dependent variable). You can then plot the corresponding point on the diagram.

In the next activity, you will produce a scatter diagram in Excel either by following the video or the instructions provided.

Activity 9 Drawing scatter diagrams

Timing: Allow approximately 35 minutes to complete this activity

The screencast in Video 5 gives you instructions on how to draw scatter plots in Excel.

Download this video clip.Video player: Video 5
Copy this transcript to the clipboard
Print this transcript
Show transcript | Hide transcript

Transcript: Video 5

NARRATOR
This video shows how to draw scatter plots in Microsoft Excel. We'll use the data file W4_Apple_Amzn_returns.xlxs to draw a scatter plot of monthly stock returns for Apple and Amazon. The data file has three columns. The first column, Date, represents calendar months. The second column, Amazon, shows monthly stock returns for Amazon. And the third column, Apple, shows monthly stock returns for Apple.
We see in the month that ended on the 1st of January, 2018, Amazon had positive stock returns of about 24%, while Apple stock went down by about 1%. The data has 25 rows, including one row for the labels, which means that our data has 24 observations of monthly stock returns for the two firms.
Let's highlight our data columns Amazon and Apple by clicking on the columns B and C. Go to Insert. Look under Charts and choose Scatter, which is under the little graph that looks like a few unconnected dots scattered around. Excel will now make a scatter plot from the highlighted data.
A scatter plot helps you visualise how two sets of numerical values are related. Each dot represents two separate measurements for a single instance. So each dot represents one month. And Amazon stock returns for that month are plotted on the x-axis, while Apple returns are plotted on the y-axis.
Once we have a scatter plot, we can add a title. We want to describe both of our measurements in the title, so we'll call this Monthly Returns, Amazon versus Apple. Next, we need to add labels for the x and y-axis. Click on the Chart area.
Go to Add Chart element. Choose Axis Titles. And click on Primary Horizontal to add the x-axis label. And choose Primary Vertical for the y-axis label. It's important that the axis labels show the units of measurement. Both the x-axis and y-axis show percentage returns, so we label them accordingly.
It can be useful to change the position of the horizontal and vertical axes. To change the position of the vertical axis, right-click on the horizontal axis and choose Format Axis. We can move the vertical axis by changing the value of the horizontal axis at the point which it crosses the vertical axis.
If we want to move the vertical axis all the way to the left, we tick Axis value and type in the lowest value for the horizontal axis. The lowest value can be seen here under Bounds. It's minus 30. So we type minus 30 here and click Enter. The y-axis has now moved all the way to the left of the graph and crosses the x-axis at minus 30.
We can move the position of the horizontal axis in the same way. Right-click on the vertical axis and choose Format Axis. To move the horizontal axis down, tick Axis value and type in the lowest value for the vertical axis. The lower vertical bound for our plot is minus 25, so type in minus 25 and click Enter. The x-axis is now moved all the way to the bottom. And the graph or scatter plot is now less cluttered.
End transcript: Video 5
Download
Video 5
Interactive feature not available in single page view (see it in standard view).

Look at the following example, which shows a data set relating to the temperature on several days in June, and the number of Pepsi drinks sold in a small shop.

Table 9 Temperature and number of drinks sold
Temperature (X) 12 14 15 17 22 13 20 23
Pepsi (Y) 12 16 16 19 32 10 24 40

Here, temperature is the independent variable and number of drinks is the dependent variable, as the sale of Pepsi is affected by changes in the temperature. Hence, you will plot temperature on the x-axis and drinks on the y-axis.

To find out if there is any relationship between variable X (Temperature) and variable Y (Pepsi), execute the following steps in Excel.

  • Select the range A1:B9
Spreadsheet of Pepsi sold and Temperature
Figure 25 Spreadsheet of Pepsi sold and Temperature
Show description|Hide description

A spreadsheet which consists of two columns. Column A is labelled as Temperature and Column B is labelled as Pepsi. From top to bottom, the Temperature column contains the values 12, 14, 15, 17, 22, 13, 20, 23. The Pepsi column contains the values 12, 16, 16, 19, 32, 10, 24, 40.

Figure 25 Spreadsheet of Pepsi sold and Temperature
  • On the Insert tab, in the Charts group, click the Scatter symbol.
The Recommended charts toolbar in Excel
Figure 26 How to select the Scatter symbol
Show description|Hide description

This figure shows a section of the Recommended charts toolbar in Excel. Over the label ‘Charts’ are different chart icons for histograms, boxplots, bar charts, pie charts and so on. the cursor hovers over the icon representing scatter plots. Next to the collection of icons is a globe icon labelled ‘Maps’ with a dropdown arrow, and a pivot chart icon labelled ‘PivotChart’, also with a dropdown arrow.

Figure 26 How to select the Scatter symbol
  • This will open a drop-down menu showing various types of scatter plots. The standard type is the one with unconnected dots in the top left. Click the Scatter symbol to insert this chart.
Section of the Scatter type selection menu in Excel
Figure 27 How to select the Scatter chart type
Show description|Hide description

This figure shows a section of the Scatter type selection menu in Excel with icons representing different styles of scatter charts arranged around the top and left edge. In the centre is text which reads: ‘Scatter. Use this chart type to: Compare at least two sets of values or pairs of data; show relationships between sets of values. Use it when: The data represents separate measurements.’

Figure 27 How to select the Scatter chart type

Comment

Scatter diagram showing the relationship between temperature and Pepsi sales
Figure 28 Temperature versus Pepsi sales
Show description|Hide description

Scatter diagram showing the relationship between Temperature (x-axis) and Pepsi sales (y-axis), with the heading ‘Pepsi versus Temperature’. The Temperature axis ranges from 0 to 25, and the Pepsi sales axis ranges from 0 to 45. The points plotted show that as temperature increases, so do the sales of Pepsi.

Figure 28 Temperature versus Pepsi sales

The final image shows the positive relationship between the number of Pepsis sold and the temperature. This shows that as the temperature increases, sales of Pepsi also increase.