Download Excel Workbook
Watch our free training video on How to Use Correlation in Excel:
Correlation in Excel is a statistical measure that gives an insight into how strongly two variables are related to one another. The result of calculating correlation is a number between 1 and -1. Numbers closer to either extreme indicate a stronger relationship, whereas a number closer to 0 indicates a weak or nonexistent relationship. A correlation statistic is tedious to calculate manually. Fortunately, Excel has the CORREL function. In this article, we will explore how to use correlation in Excel, and a couple of practical examples.
Download the Excel Workbook below to follow along and understand how to use the Correlation in Excel – download excel workbookCorrelationInExcelupdate.xlsx
Correlation Classification
Positive: As one variable increases so does the other and vice-versa. A coefficient of +1 indicates a perfect positive correlation.
Negative: As one variable increases the other has a tendency to decrease. A coefficient of -1 indicates a perfect negative correlation.
No Relationship: Movement in one variable cannot be predicted from the other. A coefficient near 0 indicates no correlation.
The CORREL Function Syntax
=CORREL(array1, array2)
Arguments:
array1 – A range of cell values.
array2 – A second range of cell values.
Positive Correlation
Let’s say we are examining the sales data of a clothing company (Column A) in relation to their advertising expenses (Column B) during the first 2 quarters.
In cell D2, enter the formula =CORREL(B2:B7, C2:C7)
As you can see, the number 0.9986885 is closer to the number 1. Indicating that there is indeed a positive correlation between the sales data and the advertising expenses. In other words, an increase in the company’s advertising expenditure corresponded to a rise in sales. Conversely, a reduction in advertising spending resulted in a decrease in sales.
To better illustrate our data, we can opt to add a line chart.
Highlight A1:C7, then Right-click.
Click on Quick Analysis, Charts, and Line.
Negative Correlation
Let’s take the same scenario to illustrate a negative correlation.
In cell D2, enter the formula =CORREL(B2:B7, C2:C7)
As you can see, the number -0.8935515 is closer to the number -1. Indicating that there is indeed a negative correlation between the sales data and the advertising expenses. In other words, an upturn in advertising expenses is correlated with a downturn in sales, and conversely, a decrease in advertising expenses aligns with an increase in sales.
No Relationship
Let’s take the same scenario to illustrate no correlation.
In cell D2, enter the formula =CORREL(B2:B7, C2:C7)
As you can see, the number -0.0376513 is closer to 0. Indicating that there is little to no correlation between the sales data and the advertising expenses. In other words, changes in advertising spending appear to have little to no impact on sales performance.
Data Analysis ToolPak
What if you need to quickly compare correlation coefficients between multiple variables? What if you don’t want to deal with formulas? This is where the Analysis ToolPak comes in handy.
The Analysis Toolpak add-in is not enabled in Excel by default. To enable it, go to the File tab.
Select Options.
Click Add-ins.
Select Excel Add-ins, and click Go.
Check the Analysis Toolpak and click OK.
Once the Analysis Toolpak is enabled, go to Data and click on Data Analysis.
Select Correlation.
Input the data range.
Click OK.
Summary:
Column 1 and 2 are positively correlated (0.99). Column 1 and 3 are negatively correlated (-0.89). Column 1 and 4 are not correlated (-0.03) . These correlations are apparent when looking at the line graph.
Notes:
If an array or reference argument comprises text, logical values, or empty cells, these values are disregarded. However, cells with zero values are taken into account.
If array1 and array2 have an uneven number of data points, CORREL produces a #N/A error.
Click here to learn How to fix the #VALUE error in Excel formulas
Should either array1 or array2 be empty, or if the standard deviation (s) of their values equals zero, CORREL results in a #DIV/0! error.
Correlation does not always imply causation. A strong correlation indicates a relationship but does not explain why it exists.
Click here to learn How to Master Excel Formulas – The Ultimate Guide!
Click here if you are Having Trouble Understanding Complex Formulas in Excel.
Click here to check out Microsoft’s tutorial on the CORREL Function!
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.
