Using MS Excel to calculate correlation coefficient

While you should learn to do manual calculation for Pearson correlation coefficient, there is no harm to use some help from MS Excel. In fact, by using Excel, you can learn better and be more confident with your answers obtained using the manual calculation. You can verify your answer by using MS Excel.

You can follow the simple four steps below to do it. 

Step 1: Enter the data in two columns. Make sure you clearly identify the X and Y variable. Typing the variable names explicitly (as opposed to using X and Y) would help you to see, and make sense of, the data better. Additionally, identify a cell for the resulting calculation. In the example below, I selected the cell under the cell with 'Correlation'.

Step 2: In the cell, type =correl. That is the formula for Pearson correlation. Then you would have to tell Excel what are the values (data) from two variables (IQ and Test Score) that you want to correlate. The variables are stated as 'array1' and 'array2'. You can type the array coordinates directly (e.g. B4:B11) or use the mouse to select the whole of the array. Please make sure you do not include the cell with the label ('IQ'). You can see 'array1' is typed using in blue.

Step 3: Insert a comma before you identify 'array2'. The second array is printed in green. 

Step 4: The last step is to complete the formula by inserting a closing bracket. So, your formula should look like this: =correl(B1:B11,C4:C11). Then, press 'Enter'. As you can see below, the correlation coefficient is 0.787802. Well done!