# How to find linear correlation coefficient in Excel

The linear correlation coefficient, also known as Pearson’s correlation coefficient, measures the strength and direction of the linear relationship between two variables. It is a value between -1 and 1. In general, if the correlation coefficient is close to -1 or +1 then we can say that the bivariate data are strongly correlated to each other. If you get a 0 value then it means that bivariate data are not related to each other. The correlation coefficient is an important tool for identifying and understanding relationships between variables and can be used in a wide range of fields and applications.

We have a dataset that includes the number of study hours of students and their corresponding marks for a test. We need to find the correlation coefficient to determine the strength and direction of the relationship between these variables. There are three methods to find the correlation coefficient. The following steps will guide you to use these methods.

## Method 1: Find the Coefficient using the Correlation function

The correlation function in Excel is a statistical function that calculates the correlation coefficient between two arrays of data. The correlation coefficient is a measure of the strength and direction of the linear relationship between two variables.

The syntax for the correlation function in Excel is:

CORREL(Array1,Array2)

where array1 and array2 are the two arrays of data for which you want to calculate the Pearson correlation coefficient.

## Step 1 – Select the Cell

• Click on the cell, where you want to show the Correlation Coefficient

## Step 2 – Use the Correlation function

• After selecting the cell, type  “=CORREL(”  in the cell to use the Correlation function

## Step 3 – Type the Arguments

• After applying the Correlation function, type its arguments
• Array 1: A2:A6
• Array 2: B2:B6
• After typing the arguments, type the closing bracket “)”

## Step 4 – Press the Enter key

• After typing the arguments, press the Enter key to get the required result

## Method 2: Find the Coefficient using the Pearson function

The Pearson function in Excel is used to calculate the Pearson correlation coefficient between two arrays of data. The Pearson correlation coefficient is a measure of the linear relationship between two variables.

The syntax for the Pearson function in Excel is:

PEARSON(array1, array2)

where array1 and array2 are the two arrays of data for which you want to calculate the Pearson correlation coefficient.

## Step 1 – Select the Cell

• Click on the cell, where you want to show the Correlation  Coefficient

## Step 2 – Use the Pearson function

• After selecting the cell, type  “=PEARSON(”  in the cell to use the Correlation function

## Step 3 – Type the Arguments

• After applying the Pearson function, type its arguments
• Array 1: A2:A6
• Array 2: B2:B6
• After typing the arguments, type the closing bracket “)”

## Step 4 – Press the Enter key

• After typing the arguments of the function, press the Enter key to get the required result

## Step 1 – Select the range of cells

• Select the range of cells for which you want to show the correlation coefficient

## Step 2 – Apply a Scatter Chart

• After selecting the range of cells, click on the Scatter chart option in the Charts option of the Insert tab
• And a chart will appear on the screen

## Step 3 – Add a Trend line

• Click on the Chart Element option at the top of the chart and a right-side menu will appear
• From this menu, click on the Trend line option to add a trend line on the chart

## Step 4 – Open the Format Trend Line dialog box

• After applying the trend line, click on the arrow next to the trend line option and a right-side menu will appear
• From this menu, click on  More options, and a Format Trend Line dialog box will appear on the right side of the sheet

## Step 5 – Click on the Display R – squared Value option

• In the Format Trend Line dialog box, click on the Display R squared Value on the chart option to get the correlation coefficient on the graph