Correlation is a measure of how closely related two variables are. So we say two variables are
correlated if knowing the value of one variable provides information on the likely value of the other,
If this is the case in our data a plot of one value against the other will have a definite
pattern and we'll see what that pattern is. In this exercise this is what our monthly CO2 data
looks like: column A has the year, column B the months which are numbered 1 to 12 - that means
1 is January and 12 is December, column C to E are different measures of CO2 emissions. So to do this
we're going to use column E which is trend data to make a scatterplot that compares CO2
emissions and temperature anomalies for month 1 - that's January. To do this exercise you're
going to need to have two spreadsheets open at the same time. The first is the CO2 data
that we're looking at, and the other one is the temperature anomaly spreadsheet. Note that some
of the data in your project might have been updated so it won't match what you see on the
screen here. Don't worry all the steps are exactly the same even if your result might be slightly different.
Now go back to the CO2 data. First we need to filter it so it shows only January.
In Windows that means you click 'Sort and Filter' in the top right, then 'Filter'. Click on the arrow
for the month and you will see the values 1 to 12. Unclick the top box to deselect all of the months
and then just click 1, hit Enter and you can see that not all the rows are visible anymore.
We are just looking at the data now for January each year.
if you're working on a Mac the Box looks
a little bit different, so here it is but as you can see the process is basically the same.
Remember the data for the other months is still there, the filters have just hidden it.
Column E has the trend data that we need. Select all the cells in Column E, click and drag on the numbers
only to highlight them all the way to the bottom of the column. In Windows Control+C copies that data
to the clipboard and for Mac users that's Command+C. Now switch to the temperature anomaly data.
We need to filter this as well so that the range of years matches. The CO2 data begins in 1959,
so we need to filter between 1959 in the top box and 2017 in the bottom box. Here's what it looks
like in Windows
and again for our Mac users here's what it's going to look like for you.
The data just copied should have the same number of rows as the data on the worksheet. Click on the
first empty column, level with the first row of numbers, type Control+V in Windows - that's Command+V on
a Mac of course, and paste the data. Type a header for the column so that other people can see what
your data is and scroll down just to check that the number of rows matches. I'm going to minimize
the CO2 data so that we can concentrate on this spreadsheet because it's the only one we're going
to use from now on. The next thing we're going to do is to make a chart called a scatterplot
from the data. First we need to select the January temperature data and then hold down the Control key -
Mac users that's the Command key, and select the CO2 data as well Both should be highlighted.
If you're not used to doing this it can be a bit tricky and take a couple of goes at first.
Click on the 'Insert' menu on the top left because we're going to insert a chart in the middle of the toolbar
that pops up when you click insert there's a gallery of chart types pick the scatterplot icon -
it has little dots on it, and select the first chart. The basic scatter plot should pop up in
the middle of your data. If you are making a report you'd move it. Let's leave it there for now and at
quick look it, it looks like the data is correlated because when CO2 is higher so is the temperature.
But it's not very clear at the moment and we can make this easier to look at by adjusting the scale
of the vertical axis. To do this, position the cursor over the vertical axis and Windows users
click the right button on the mouse - Mac users you have to hold down the Control key this time
while you click the mouse button. A menu should pop up hovering over the chart. Select 'Format axis' and
click on the axis options. The minimum bound is Excel's name for the point at which the vertical
axis crosses the horizontal axis. None of our data points are lower than 250 so we can type that
into the box. Close the access options box and you will see that the scatterplot is much clearer now
But at the moment no one else in looking at this chart would know what it was - even you are going to struggle
to remember it yourself when you open it up next week. So always, always, always label your
chart and both of the axes. To do this click on the chart, click 'Design' and you'll see placeholders
for the axes in the title. We're going to type short titles for speed but it's always better
to be precise - for example use years, locations, or data sources when you label your charts.
Our last step is to calculate the correlation. To do this we're going to use a statistic
called the correlation coefficient. It tells us how close the data is to a straight line on a scatterplot.
The correlation coefficient ranges in value from -1 to 1. If the coefficient is 1,
then the data is a straight line sloping upwards and if it's -1 it is a straight
line sloping downwards, and if it's 0 then the variables are probably not related to each other.
Looking at our scatterplot we can see something that's a bit like a straight line going upwards
and so we should not expect the correlation to be 0 and we definitely wouldn't expect it
to be negative but Excel offers a formula to calculate the correlation exactly. Select an
empty column, label the cell at the top so that we know that the number is the correlation
and in the first blank row type this formula: =CORREL and an open bracket and now we need the arguments.
First select the temperature anomaly data we using which is column B
Add a comma, select the CO2 data and close the bracket.
This is what the formula should look like. You can see how it represents the
two data series. You can always type these cell references directly into the formula.
So the last thing to do is to see what it's calculated for our correlation and as you
can see the number is positive and it's close to 1, as we expected. Remember when calculating
statistics like this always check that the outcome seems to match what you see on the chart.
It's a simple step to make sure that you haven't accidentally selected the wrong data.