How to calculate the correlation coefficient in Excel

You can watch a video tutorial here.

The correlation coefficient is a metric that measures the relationship between 2 datasets. In Excel, this can be done using the data analysis tool or by using the CORREL() function.

  1. CORREL(): this returns the correlation coefficient of 2 sets of data
    1. Syntax: CORREL(array1, array2)
      1. Array1: the first dataset
      2. Array1: the second dataset

Option 1 – Use the Data Analysis tool

Note: If the Data Analysis button is on the Data > Analyze ribbon, then skip Steps 1 to 3 below. 

Step 1 – Open the Excel Options window

  • Go to File > Options

Step 2 – Manage the Add-ins

  • Go to Add-ins
  • Select Excel Add-ins from the Manage drop-down
  • Click Go

Step 3 – Load the Analysis ToolPak add-in

  • Select Analysis ToolPak
  • Click OK

Step 4 – Open the Correlation window

  • Go to Data > Analyze
  • Click on the Data Analysis button
  • In the window, select Correlation
  • Click OK

Step 5 – Set the parameters

  • Define the input range:
    • Input Range: the range of both the ‘Selling Price’ and ‘KM_driven’ columns
    • Tick the Labels in first row box 
    • Output Range: the location where the result is to be displayed
  • Click OK

Step 6 – Check the result

  • The correlation coefficient between the variables is displayed

Option 2 – Use the CORREL() function

Step 1 – Create the formula

  • Select the cell where the result has to be displayed
  • Type the formula using cell references:

=CORREL(range of Selling_price, range of KM_driven)

  • Press Enter

Step 2 – Check the result

  • The correlation coefficient is displayed