Pinterest Pixel

Correlation in Excel – With Examples!

Correlation in Excel is a statistical measure that gives an insight into how strongly two variables are... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Correlation in Excel - With Examples! | MyExcelOnline Correlation in Excel - With Examples! | MyExcelOnline

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)

See also  Excel Paste Special: A Values Multiplier

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)

Correlation in Excel - With Examples!

Correlation in Excel - With Examples!

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.

Correlation in Excel - With Examples!

 

Click on Quick Analysis, Charts, and Line.

Correlation in Excel - With Examples!

Correlation in Excel - With Examples!

Negative Correlation

Let’s take the same scenario to illustrate a negative correlation.

In cell D2, enter the formula =CORREL(B2:B7, C2:C7)

Correlation in Excel - With Examples!

Correlation in Excel - With Examples!

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.

See also  Convert Values to Dates Using Flash Fill In Excel

No Relationship

Let’s take the same scenario to illustrate no correlation.

In cell D2, enter the formula =CORREL(B2:B7, C2:C7)

Correlation in Excel - With Examples!

Correlation in Excel - With Examples!

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.

Correlation in Excel - With Examples!

Click Add-ins.

Select Excel Add-ins, and click Go.

Check the Analysis Toolpak and click OK.

Correlation in Excel - With Examples!

Once the Analysis Toolpak is enabled, go to Data and click on Data Analysis.

Correlation in Excel - With Examples!

Select Correlation.

Correlation in Excel - With Examples!

Input the data range.

See also  3 Quick Ways on How To Create A List In Excel

Correlation in Excel - With Examples!

Click OK.

Correlation in Excel - With Examples!

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!

If you like this Excel tip, please share it
Correlation in Excel - With Examples! | MyExcelOnline Correlation in Excel - With Examples! | MyExcelOnline
See also  Insert Grand Totals to a Pivot Table
Founder & Chief Inspirational Officer at MyExcelOnline.com

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.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...