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

Reach your personal and professional goals

Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.

Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.

Start Learning now