Correlation Excel Formula

In Microsoft Excel, correlation measures the association strength between quantitative variables. It is usually in the form of correlation coefficient. The correlation coefficient value is symbolized by a Greek letter “rho”. It ranges from negative one for its negative perfect correlation to zero. To contain no correlation, positive one is for the positive perfect correlation. The spreadsheet program or Microsoft Excel can analyze the correlation level between the two variables using the analysis data function.

How to Measure Correl in Excel

  • Enter a certain data to be analyzed. If the user is to examine the correlation between two certain variables, one variable can definitely represent a single column within the spreadsheet. Enter certain values of every variable in the cells, which is under the corresponding column.
  • Install and unlock the “data analysis tool” from the menu of Microsoft Excel add-ins. This tool is considered and included within the entire Excel options. However, it must be installed before the user can use it. In Microsoft Excel 2007, the user must click the Help button on the top left corner of the spreadsheet. Then, you have to click Excel options. Select “add-ins” and choose “Analysis ToolPak”. Thus, it makes the data analysis tool available for use.
  • Select two variables to examine the correlation. Upon choosing the values to be analyzed in Microsoft Excel, the user is required to click on the column or cell then drag the mouse to define or choose the range of values of the two variables, which is under the said consideration.
  • Open the Data Analysis Tool in Microsoft Excel to display the window having the analysis option sets. Select the ”correlation” button then click the “OK” button. This will open the correlation wizard. It asks the user to enter the data range to be analyzed through clicking the cell or column and dragging.
  • Examine the correlation coefficient value wherein Microsoft Excel will display within the new worksheet. Keep in mind that the correlation indicates the connection between two certain variables. However, it doesn’t prove ay causation.

In Microsoft Excel, there are innumerable functions and formulas, which are important in solving many problems of the user while creating data in the spreadsheet. One of these functions is called the CORREL function in Microsoft Excel. Thus, CORREL function in Microsoft Excel returns the coefficient of both the cell ranges belonging to both array 1 and array 2. It uses the correlation coefficient in determining the relationship between the two properties. For instance, the user can examine the correlation between the averages of the utilization of the air conditioners as well as the location temperature. The syntax for the CORREL function in Microsoft Excel is CORREL (array1, array2). Array1 has been considered as the first cell range of the values. Array2 is considered as the subsequent sect range of values.


  • If the cell reference of array argument contains logical values, text and empty cells, these values are being ignored. On the other hand, cells with a zero value are significantly included.
  • If the array2 and array1 with different data number points, the CORREL function returns the #N/A error value.
  • If either of the array2 or array1 is empty, the standard deviation of the given values is equal to zero, and then CORREL function returns the #DIV/0! Error value.

Instructions in Using CORREL Function

  • Open first the Microsoft Excel. The user can do it through clicking the start button then select “All Programs” and double-click the Microsoft Excel icon on the window.
  • Before working with the data, it is really imperative to understand the essential fact concerning the correlation coefficient. This is the value which represents the relationship strength between two random variables. The said coefficient correlation.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>