Correlation and the correlation coefficient are often discussed in many financial sectors such as the stock market and investment portfolio analysis. This mainly determines the relationship between two variables. In this article, I will discuss **correlation** and show you **3** simple ways to find a correlation between two variables in Excel.

**Table of Contents**hide

## Download Practice Workbook

You can download our practice workbook from here for free!

## What Is Correlation?

**Correlation** is a statistical measure that indicates whether there is a relationship between two variables. Correlation measures the strength of the relationship between two variables, as well as whether or not there is a positive or negative relationship between the two variables.

The discussion of correlation is prevalent in many financial sectors. Financial papers and analysts often evaluate the correlation between the price of gold and let’s say a certain stock.

A positive correlation basically means that as one variable increases, so does the other, whereas a negative correlation refers to a situation where one variable increases, and the other decreases. This is mainly determined by the correlation coefficient value.

## Correlation Coefficient Interpretation

The values for the **correlation coefficient**, r fall in the range of +1.0 to -1.0, depending on the strength of the relationship between the two variables.

- An r of 0 indicates that there is no relationship between the two variables.
- An r of +1.0 describes a perfect positive correlation between two variables whereas an r of -1.0 describes a perfect negative correlation.

**Notes:**

- There is one main issue to note with the correlation coefficient and that is that it should not be used to denote a cause and effect relationship. For example, smoking causes an increase in the risk of lung cancer, whereas smoking and alcoholism are correlated (one does not cause the other in this scenario).
- It is very important to note that there may be another variable affecting the relationship between two variables and therefore not use correlation as a causation indicator.

## 3 Simple Ways to Find Correlation Between Two Variables in Excel

Say, a store manager working at a hypothetical department store is looking at the relationship between the monthly number of free complimentary makeovers given by the beauty department and the monthly sales of makeup sets in that specific department. The sample dataset is given below.

You can follow any of the 3 ways given below to find the correlation between these two variables in Excel.

### 1. Using the CORREL Function in Excel 2007 | 2010 | 2016 or More

The simplest way to find the correlation between two values is to use the **CORREL function**. Follow the steps below to do this.

**📌 Steps:**

- First and foremost, click on the
**C13**cell and insert the following formula.

`=CORREL(B5:B11,C5:C11)`

- Subsequently, press the
**Enter**button.

Thus, you will be able to calculate the correlation coefficient of the two selected variables dataset. And, thus you can say they are positively related to each other. For instance, the result sheet would look like this.

**Read More:** **How to Calculate Pearson Correlation Coefficient in Excel (4 Methods)**

**Similar Readings**

**How to Make Correlation Graph in Excel (with Easy Steps)****Calculate Intraclass Correlation Coefficient in Excel****How to Make a Correlation Matrix in Excel (2 Handy Approaches)****Calculate Autocorrelation in Excel (2 Ways)**

### 2. Use Data Analysis ToolPak to Find Correlation Between Two Variables

Another famous and frequent way to find correlation coefficients is to use the Excel Data Analysis ToolPak. Go through the steps below to achieve your target in this way.

**📌 Steps:**

- At the very beginning, go to the
**File**tab from your Excel ribbon.

- Afterward, choose
**Options**from the expanded**File**tab.

- As a result, the
**Excel Options**window will appear. - Following, go to the
**Add-ins**tab of this window >> choose**Excel Add-ins**at the**Manage:**options list >> choose the**Analysis ToolPak**from the**Inactive Applications Add-ins**list >> click on the**Go…**button.

- Consequently, the
**Add-ins**window will appear. - Subsequently, choose the option
**Analysis ToolPak**and click on the**OK**button.

- Thus, you will see there will be a new tool named
**Data Analysis**in the**Data**tab. - Now, go to the
**Data**tab >>**Data Analysis**tool.

- As a result, the
**Data Analysis**window will appear. - Following, click on the
**Correlation**option and click on the**OK**button.

- At this time, the
**Correlation**window will appear. - Subsequently, choose the
**Input Range:**as**B4:C11**>> choose**Columns**option in the**Grouped By:**options >> tick on the**Labels in first row**option. - Following, from the
**Output options**group, put the radio button on the**Output Range:**option and refer to cell**B13**. - Last but not least, click on the
**OK**button.

As a result, the **correlation matrix** will appear on the **B13** cell and you will get your desired correlation coefficient for these variables. And, the final outcome should look like this.

**Read More:** **How to Calculate Cross Correlation in Excel (2 Quick Ways)**

### 3. Use PEARSON Function in Excel to Find Correlation

Moreover, you can use the **PEARSON function** to calculate the correlation coefficient. Follow the steps below to accomplish this.

**📌 Steps:**

- First, click on the
**C13**cell and write the following formula.

`=PEARSON(B5:B11,C5:C11)`

- Afterward, hit the
**Enter**button.

As a result, you will get the correlation coefficient for these two arrays and thus, you can find the correlation between two variables in Excel. For instance, the final output should look like this.

## Calculate Correlation Coefficient in Excel Graph

Calculating and displaying correlation coefficients in Excel graphs is a frequent need for many of us. You can easily accomplish this by following the steps below.

**📌 Steps:**

- First and foremost, select the variables’ dataset (
**B5:C11**here). - Subsequently, go to the
**Insert**tab >>**Insert Scatter (X,Y) or Bubble Chart**tool >>**Scatter**option.

- As a result, you will get the scatter chart for your selected dataset.

- For a cleaner and better look, click on the
**Chart Elements**icon >> tick on the**Axes**option and the**Trendline**option.

- Afterward, click on the
**Chart Elements**icon >>**Rightward**arrow beside the**Trendline**option >>**More Options…**option.

- As a result, the
**Format Trendline**pane will appear on the right side of the Excel. - Here, go to the
**Trendline Options**group >> tick on the**Display R-squared value on chart**option.

As a result, you will see that the R-squared value is shown inside the graph. This is the correlation coefficient squared value. So, the final output should look like this.

## Multiple Correlation in Excel: Correlation Among More Than Two Variables

Now, it might happen that you have more than two variables in your dataset. And, you need to find the correlation between each pair of variables.

So, you have to find multiple correlations here. One is between the Sales of Makeup Sets per Month and the Free Complimentary Makeovers Given per Month. Another one is between the Extra Profit per Month and the Free Complimentary Makeovers Given per Month. To achieve this target, follow the steps below.

**📌 Steps:**

- Initially, click on the
**C14**cell and insert the following formula for the first and second variable’s correlation.

`=CORREL(B5:B11,C5:C11)`

- Subsequently, hit the
**Enter**button. - Similarly, for the first and third variable’s correlation, click on the
**D14**cell and insert the following formula.

`=CORREL(B5:B11,D5:D11)`

- Following, press the
**Enter**button.

Thus, you have found multiple correlations between multiple variables and the final result should look like this.

**Note:**

You can also use the other 2 ways stated above to find multiple correlations in Excel.

## 💬 Things to Remember

- We cannot use these correlation results to indicate a cause and effect relationship, since the increase in sales of makeup sets per month may also be influenced by other factors such as an increase in ads in print media advertising the makeup sets for example.
- If you want to find the exact cause and effect relationship, you will have to use
**linear regression**.

## Conclusion

So, in this article, I have shown you 3 simple and suitable ways to find correlations between two variables in Excel. I suggest you read the full article carefully and practice accordingly to understand better. I hope you find this article helpful and informative. You are very welcome to comment here if you have any further questions or recommendations.

And, visit **ExcelDemy** to learn about many more Excel problem solutions, tips, and tricks. Thank you!

**Related Articles**

**How to Make a Correlation Scatter Plot in Excel (2 Quick Methods)****Calculate Spearman Correlation in Excel (3 Easy Methods)****How to Interpret Correlation Table in Excel (A Complete Guideline)****Calculate P Value for Spearman Correlation in Excel****How to Find Spearman Rank Correlation Coefficient in Excel (2 Ways)**