Skip main navigation

Calculating correlation with spreadsheets

How to calculate the correlation in an Excel spreadsheet.

To calculate the correlation in an Excel spreadsheet, you can use the CORREL() function.

Let’s look at an example.

Consider these two lists of data in an Excel spreadsheet:

Excel spreadsheet showing columns A and B each column listing values between 2 and 11

To determine the correlation between them:

  1. Select a blank cell at the bottom of column B and enter the formula: =CORREL(A2:A7, B2:B7) where A2:A7, B2:B7 represent the range of data to include.

  2. Click Enter. Excel calculates the correlation coefficient.

Excel spreadsheet showing columns A and B values being correlated using an excel formula. The total correlation is .996, almost total correlation

According to the previous section’s diagram this would be a strong correlation value.

Tableau also provides a handy way to calculate correlation using the function CORR() to calculate the Pearson Correlation Coefficient which can then be used to create a correlation value matrix: Link

Which produces this:

tableau correlation 1

Note: Image produced using Tableau.

This matrix helps with shopping basket analysis. When one category of product was bought, what other product categories were also bought in the same order. Any cells highlighted in darker blue would indicate a moderate to strong positive correlation and orange cells indicate a weak negative correlation

Next, there is a quiz where you can reflect on what you know about direct and indirect relationships in regard to coorelation so far.

This article is from the free online

SQL for Data Analysis

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