How to Do Correlation in Excel (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Correlation is a type of measure, which mainly uses to show the variation between two data. It also describes the strength and direction of two variables. People mainly use these correlation features in statistics, economics, and social sciences for getting relations in business plans, budgets, and so on. If you are facing any difficulties to do and are interested to know how to do correlation in Excel, then follow us.


What Is Correlation?

Correlation is a non-dimensional value. It shows us the relationship between two variables. In addition, it provides us with an indication of the relationship among those variables by evaluating and relating the variance of each variable. Correlation contains two major functions of statistical calculation. They are Covariance and Standard Deviation. The range of the correlation’s value moves between -1 to +1. The formula of correlation is:

Here, A and B are two different variables.

Let’s explain the whole idea with a simple mathematical example.

We have 3 values of variables A and B. The values are shown below:

How to Do Correlation in Excel

Now, according to the formula calculate the value of Covariance(A, B), Std deviation of A, and Std deviation of B. Put those values in the equation shown above. You will get the value of Correlation.

How to Do Correlation in Excel


How to Do Correlation in Excel: 3 Easy Methods

For determining the value of the correlation of two variables, we are considering a dataset where the income and expense of a person for the first 10 months are shown. The name of the months is in column B, and the income and the expanse are in columns C and D respectively. So, we can say that our dataset is in the range of cells B4:D14.


1. Using CORREL Function

In this method, we are going to use the CORREL function to calculate the value of the correlation of our dataset. Our dataset is in the range of cells B4:D14 and the value of the correlation will be in cell C16. The steps of this method are given below:

📌 Steps:

  • At first, select cell C16.
  • Now, write down the following formula in the cell.

=CORREL(C5:C14,D5:D14)

Using CORREL Function to Do Correlation in Excel

  • Press the Enter key on your keyboard to get the result.
  • You will see the value correlation of our dataset is shown in cell C16.

Using CORREL Function to Do Correlation in Excel

💬 Explanation of the Value for Correlation Used in the Dataset

The value of correlation shows us the strength of the relationship between the values of two variables. So, the value of 0.919 indicates that for every row the values are 0.919 times each other. Moreover, this value is the ratio of the covariance of both variables and the multiplication of the standard deviation of Income and the standard deviation of Expanse. A graph is displayed below for a better understanding of this feature.

Finally, we can say that our formula worked successfully and we can estimate the value of the correlation.


2. Applying PEARSON Function

In the following approach, we will use the PEARSON function to calculate the value of the correlation. Our dataset is in the range of cells B4:D14 and the value of the correlation will be in cell C16. The procedure of this method is given as follows:

📌Steps:

  • First of all, select cell C16.
  • Then, write down the following formula into the cell.

=PEARSON(C5:C14,D5:D14)

Applying PEARSON Function to Do Correlation in Excel

  • After that, press the Enter key.
  • You will get the value correlation of our dataset is showing in cell C16.

Applying PEARSON Function to Do Correlation in Excel

💬 Explanation of the Value for Correlation Used in the Dataset

We know that the value of correlation helps us to understand the strength of the relationship between the values of two variables. For our dataset, the value of 0.919 indicates that for every row the values are 0.919 times each other. Besides it, this value is the ratio of the covariance of both variables and the multiplication of the standard deviation of Income and the standard deviation of expense. A graph is displayed below for a better understanding of this feature.

Thus, we can say that our formula worked perfectly and we are able to do correlation in Excel.

Read More:


3. Use of Excel’s Built-in Data Analysis Feature

In this process, we will use Excel’s built-in Data Analysis features to do correlation in the Excel dataset. To use this feature, we have to enable the Data Analysis option from Excel’s built-in Add-ins. Our dataset is in the range of cells B4:D14 and the result will be in the range of cells B16:D18. The process is explained below step by step:

📌 Steps:

  • To enable the Data Analysis option, first select File > Options.

  • A dialog box called Excel Options will appear.
  • In this box, select the Add-ins option.
  • After that, choose the Manage option as Excel Add-ins and click Go.

Use of Excel’s Built-in Data Analysis Feature to Do Correlation in Excel

  • Another dialog box entitled Add-ins will appear.
  • Check the Analysis ToolPak option.
  • Finally, click OK.

Use of Excel’s Built-in Data Analysis Feature to Do Correlation in Excel

  • You will see a new group called Analysis added in the Data tab.

  • Now, select the Data Analysis option from the Analysis group.
  • A dialog box titled Data Analysis will appear.
  • Then, choose the Correlation option and click OK.

Use of Excel’s Built-in Data Analysis Feature to Do Correlation in Excel

  • Another dialog box called Correlation will come.
  • After that, set the Input Range as $C$5:$D$14, selecting through your mouse.
  • Choose the Output options as Output Range and write your desired cell. In our case, we choose cell $B$16.
  • Al last clicks the OK option.

Use of Excel’s Built-in Data Analysis Feature to Do Correlation in Excel

  • Finally, you will see the value of correlation is showing in the range of cells B16:D18.

Use of Excel’s Built-in Data Analysis Feature to Do Correlation in Excel

💬 Explanation of the Value for Correlation Used in the Dataset

The value of correlation indicates the strength of the relationship between the values of two variables. Thus, the value of 0.919 in cell B18 indicates that for every row the values are 0.919 times each other. Besides it, the value of B17 and C18 shows the correlation value against their own columns, thus those value returns 1. This value is the ratio of the covariance of both variables and the multiplication of the standard deviation of Income and the standard deviation of Expanse.

So, we can say that our approach worked successfully and we are able to do correlation in Excel.

Read More:


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


Conclusion

That’s the end of this article. I hope that this content will be helpful for you and you will be able to do correlation in your Excel workbook. If you have any further queries or recommendations, please share them with us in the comments section below.


Excel Correlation: Knowledge Hub


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Soumik Dutta
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo