1. Home
  2. MS Office

Find Correlation Between Datasets In Excel 2010

Excel 2010 supports a multitude of statistical functions that bring ease for calculating statistical data promptly. Amongst Stats functions, finding a correlation manually between two given datasets can be cumbersome, but with built-in Excel 2010 CORREL function, finding co-relation coefficient is a cinch. The function lets you easily calculate co-relation between any two given data sets or set of values. You need to specify the data range or values, it instantly shows you the co-relation between them. You can quickly apply applied CORREL function over required data sets by just copying the cell containing the CORREL function. All it requires is two simple arguments to be filled in the form of arrays. The Correlation values range from –1.0 to +1.0, implying +1.0 as best co-relation between datasets and converse is the case with –1.0.

To begin, launch Excel 2010 and open a spreadsheet you want to use the co-relation. For instance we have created two fields namely Values 1 and Values 2.

table 1

Correlation resultant values ranges from –0.1 to +0.1, where +1.00 being the best positive relationship you can get. We will find out coefficient correlation between above mentioned data values field.

To start off with, first you need to familiar with the following syntax.


array1 in function argument is the dataset 1 and array2 refers to dataset 2. Under Values 1 and Values 2 fields, we will add a label namely Correlation Coefficient.


We will write the CORREL with the following data ranges.


In function argument, A2:A11 is the range of dataset 1, in mentioned spreadsheet that is Values 1 field, and B2:B11 is the range of field Values 2. We will be putting this formula underneath the Correlation Coefficient label. This will instantly show the result for values residing in both fields.


The result 0.299 shows, which is not a perfect correlation between fields but not that much negative as it is in the range of 0 to +1

You may also be interested in previously reviewed on Excel Functions; MROUND, MODULUS & QUOTIENT, RANK.AVG and RANK.EQ, WORKDAY.INTL, LARGE, CEILING, TIME, ADDRESS ,FACT (factorial), MAX,MIN, MAXA, MINA and EXACT, Logical Functions, INFO, SUMSQ, DOLLAR, SUMPRODUCT, SUMIF, COUNTIF, VLOOKUP, HLOOKUP ,PMT, & LEN.

Leave a comment