Correlation in Excel: coefficient, matrix and graph

Correlation in Excel - the basics

Correlation is a measure that describes the strength and direction of a relationship between two variables. It is commonly used in statistics, economics and social sciences for budgets, business plans and the like.

The method used to study how closely the variables are related is called correlation analysis.

Here are a couple of examples of strong correlation:

• The number of calories you eat and your weight (positive correlation)
• The temperature outside and your heating bills (negative correlation)

And here the examples of data that have weak or no correlation:

• Your cat's name and their favorite food
• The color of your eyes and your height

An essential thing to understand about correlation is that it only shows how closely related two variables are. Correlation, however, does not imply causation. The fact that changes in one variable are associated with changes in the other variable does not mean that one variable actually causes the other to change.

If you're interested to learn causality and make predictions, take a step forward and perform linear regression analysis.

Correlation coefficient in Excel - interpretation of correlation

The numerical measure of the degree of association between two continuous variables is called the correlation coefficient (r).

The coefficient value is always between -1 and 1 and it measures both the strength and direction of the linear relationship between the variables.

Direction

The coefficient sign (plus or minus) indicates the direction of the relationship.

• Positive coefficients represent direct correlation and produce an upward slope on a graph - as one variable increases so does the other, and vice versa.
• Negative coefficients represent inverse correlation and produce a downward slope on a graph - as one variable increases, the other variable tends to decrease.

For better understanding, please take a look at the following correlation graphs:

• A coefficient of 1 means a perfect positive relationship - as one variable increases, the other increases proportionally.
• A coefficient of -1 means a perfect negative relationship - as one variable increases, the other decreases proportionally.
• A coefficient of 0 means no relationship between two variables - the data points are scattered all over the graph.

Pearson correlation

In statistics, they measure several types of correlation depending on type of the data you are working with. In this tutorial, we will focus on the most common one.

Pearson Correlation, the full name is the Pearson Product Moment Correlation (PPMC), is used to evaluate linear relationships between data when a change in one variable is associated with a proportional change in the other variable. In simple terms, the Pearson Correlation answers the question: Can the data be represented on a line?

In statistics, it is the most popular correlation type, and if you are dealing with a "correlation coefficient" without further qualification, it's most likely to be the Pearson.

Here's the most commonly used formula to find the Pearson correlation coefficient, also called Pearson's R:

At times, you may come across two other formulas for calculating the sample correlation coefficient (r) and the population correlation coefficient (ρ).

How to calculate correlation coefficient in Excel

To compute a correlation coefficient by hand, you'd have to use this lengthy formula. To find correlation coefficient in Excel, leverage the CORREL or PEARSON function and get the result in a fraction of a second.

How to make a correlation matrix in Excel with Data Analysis

When you need to test interrelations between more than two variables, it makes sense to construct a correlation matrix, which is sometimes called multiple correlation coefficient.

The correlation matrix is a table that shows the correlation coefficients between the variables at the intersection of the corresponding rows and columns.

The correlation matrix in Excel is built using the Correlation tool from the Analysis ToolPak add-in. This add-in is available in all versions of Excel 2003 through Excel 2019, but is not enabled by default. If you have not activated it yet, please do this now by following the steps described in How to enable Data Analysis ToolPak in Excel.

With the Data Analysis tools added to your Excel ribbon, you are prepared to run correlation analysis:

1. On the top right corner of the Data tab > Analysis group, click the Data Analysis button.
2. In the Data Analysis dialog box, select Correlation and click OK.
3. In the Correlation box, configure the parameters in this way:
   • Click in the Input Range box and select the range with your source data, including column headers (B1:D13 in our case).
   • In the Grouped by section, make sure the Columns radio box is selected (given that your source data are grouped into columns).
   • Select the Labels in first row check box if the selected range contains column headers.
   • Choose the desired output option. To have the matrix in the same sheet, select Output Range and specify the reference to the leftmost cell into which the matrix is to be output (A15 in this example).

When done, click the OK button:

Your matrix of correlation coefficients is done and should look something like shown in the next section.

How to do multiple correlation analysis in Excel with formulas

Building the correlation table with the Data Analysis tool is easy. However, that matrix is static, meaning you will need to run correlation analysis anew every time the source data change.

The good news is that you can easily build a similar correlation table yourself, and that matrix will update automatically with each change in the source values.

To have it done, use this generic formula:

In our case, the first variable range is $B$2:$B$13 (please notice the $ sign that locks the reference), and our correlation formula takes this shape:

=CORREL(OFFSET($B$2:$B$13, 0, ROWS($1:1)-1), OFFSET($B$2:$B$13, 0, COLUMNS($A:A)-1))

With the formula ready, let's construct a correlation matrix:

1. In the first row and first column of the matrix, type the variables' labels in the same order as they appear in your source table (please see the screenshot below).
2. Input the above formula in the leftmost cell (B16 in our case).
3. Drag the formula down and to the right to copy it to as many rows and columns as needed (3 rows and 3 columns in our example).

As the result, we've got the following matrix with multiple correlation coefficients. Please notice that the coefficients returned by our formula are exactly the same as output by Excel in the previous example (the relevant ones are highlighted):

How to plot a correlation graph in Excel

When doing correlation in Excel, the best way to get a visual representation of the relations between your data is to draw a scatter plot with a trendline. Here's how:

1. Select two columns with numeric data, including column headers. The order of columns is important: the independent variable should be in the left column as this column is to be plotted on the x axis; the dependent variable should be in the right column as it will be plotted on the y axis.
2. On the Inset tab, in the Chats group, click the Scatter chart icon. This will immediately insert an XY scatter chart in your worksheet.
3. Right click any data point in the chart and choose Add Trendline… from the context menu.

For the detailed step-by-step instructions, please see:

• How to create scatter plot in Excel
• How to add trendline to Excel chart

For our sample data set, the correlation graphs look like shown in the image below. Additionally, we displayed R-squared value, also called the Coefficient of Determination. This value indicates how well the trendline corresponds to the data - the closer R² to 1, the better the fit.

From the R² value displayed on your scatterplot, you can easily calculate the correlation coefficient:

1. For better accuracy, get Excel to show more digits in the R-squared value than it does by default.
2. Click the R² value on the chart, select it using the mouse, and press Ctrl + C to copy it.
3. Get a square root of R² either by using the SQRT function or by raising the copied R² value to the power of 0.5.

For example, the R² value in the second graph is 0.9174339392. So, you can find the correlation coefficient for Advertising and Heaters sold with one of these formulas:

=SQRT(0.9174339392)

=0.9174339392^0.5

As you can make sure, the coefficients calculated in this way are perfectly in line with the correlation coefficients found in the previous examples, except the sign:

Potential problems with correlation in Excel

The Pearson Product Moment Correlation only reveals a linear relationship between the two variables. Meaning, your variables may be strongly related in another, curvilinear, way and still have the correlation coefficient equal to or close to zero.

The Pearson correlation is not able to distinguish dependent and independent variables. For example, when using the CORREL function to find the association between an average monthly temperature and the number of heaters sold, we got a coefficient of -0.97, which indicates a high negative correlation. However, you could switch around the variables and get the same result. So, someone may conclude that higher heater sales cause temperature to fall, which obviously makes no sense. Therefore, when running correlation analysis in Excel, be aware of the data you are supplying.

Besides, the Pearson correlation is very sensitive to outliers. If you have one or more data points that differ greatly from the rest of the data, you may get a distorted picture of the relationship between the variables. In this case, you'd be wise to use the Spearman rank correlation instead.

Practice workbook

Calculate Correlation in Excel (.xlsx file)