In statistical analysis, performing **Correlation **and** Regression **calculations is the popular way to analyze and predict future events. However, there are several ways to calculate **Correlation** and **Regression** for a specific set of data. In this article, we will show a simple demonstration of **how to do Correlation and Regression Analysis in Excel**.

The below image depicts a complete output of Linear Regression analysis which you will get upon completion of the task.

**Table of Contents**hide

## Download Practice Workbook

You can download and practice the dataset that we have used to prepare this article.

## Introduction to Correlation and Regression

**Correlation** is an expression of how closely two variables are linearly related to each other. It is a typical technique for describing apparent connections without explicitly stating any cause and consequence.

In statistical analysis, **Correlation** is measured by a coefficient denoted by “**r**”.

Let’s assume a set of data labeled by two variables X and Y. Thus the **Correlation Coefficient** can be formulated such as

Where,

**x̄**and**ȳ**are the sample means**AVERAGE**of**X**variable and**AVERAGE**of**Y**variable respectively.

On the other hand, **Regression **analysis is a statistical technique devoted to estimating the connection between one dependent and two or more independent variables. It can be used to simulate the long-term link between variables and evaluate the future outcome of the dependent variable. For Linear **Regression** Analysis, a linear line equation can be formulated as below,

**Y=mX+C**

Where,

**Y**is the dependent variable, and**X**is the independent variable.**m**is the slope of the straight line.

Here, we have chosen a dataset named “**Financial Statement of ABC in First Week**” to accomplish our task. However, you may select any dataset that is suitable for you.

Here, we have used the **Microsoft Excel 365 **version; you may use any other version according to your convenience.

## 2 Quick Steps to Perform Correlation and Regression Analysis in Excel

In Excel, there are several methods to perform **Correlation** and **Regression** analysis, such as using the **CORREL**, **PEARSON**, **INTERCEPT**, **SLOPE** functions, or utilizing **Scatter Chart**, **Analysis ToolPak** Add-in, etc. However, as our primary objective is to make an Excel sheet with both **Correlation **and** Regression** analysis for one specific dataset, we will incorporate two different methods to accomplish the task. However, in the following section, we will add one more method to help you understand the relevant methods and functions.

### Step 1: Calculate the Correlation Coefficient

- Write the following formula in cell
**C13**.

`=CORREL(C5:C11,D5:D11)`

- Press the
**ENTER**button to see the output.

### Step 2: Compute the Constant and Intercepting value for Regression Line

- Enter the following formula in cell
**C16**to calculate the slope value.

`=SLOPE(D5:D11,C5:C11)`

- Hit the
**ENTER**button to see the output.

- Following that, write the following formula in cell
**C17**to compute the intercepting value of the**Regression**line.

`=INTERCEPT(D5:D11,C5:C11)`

- Hit the
**ENTER**button afterward.

Here, the **Correlation Coefficient** indicates how closely the data point aligns with the **Regression** line. The given dataset’s correlation coefficient value of **0.952** indicates that the data points are closely aligned with the created trendline. However, the **Slope** value of the **Regression** line shows the amount of profit change per the number of cars sold. The intercept value indicates that the profit will be** -11.940 **when the number of cars sold is **zero**.

## How to Do Correlation Analysis in Excel

Although we have seen one of the ways to find the** Correlation Coefficient** for a set of data in the previous method, here we are adding one more method at your disposal for your better understanding. Other than the **CORREL** function, here we will use the **PEARSON** function to accomplish the task.

📌 ** Steps**:

- Go to the
**C13**cell >> enter the formula as given below >> press the**ENTER**key.

`=PEARSON(C5:C11,D5:D11)`

## How to Accomplish Regression Analysis in Excel

There are several methods to perform a **Regression **analysis in Excel. Here, we will see how to do **Regression** analysis by using an **Excel Chart**.

📌 ** Steps**:

- Select the range of the dataset from
**C5:D11**, then navigate to the**Insert**tab >>**Charts**group >>**Insert Scatter or Bubble Chart**group >>**Scatter**.

You will get an output as given below

- After that, click one of the data points on your chart, then do Right-click on your mouse.
- Press on the
**Add Trendline**option.

- Now on the right side of your Excel interface, a wizard will appear.
- Select
**Linear**as your**Trendline**option. - Later, select
**Display Equation on Chart**.

Finally, you will get the final output along with the** Trendline** as given below.

## Practice Section

We have provided a** Practice** section on the right side of each sheet so you can practice yourself. Please make sure to do it yourself.

## Conclusion

In this article, we have discussed **how to do Correlation and Regression Analysis in Excel**. As you have already understood, there are plenty of ways to do this task. So before going through a specific method, ensure the method you choose aligns with your work. Further, If you have any queries, feel free to comment below and we will get back to you soon.