You need internet connection for this.You have to first load the analysis toolpak. To do this you have got to file tab and choose the options from the left column. Click on Add-in category located on the left side of screen.Now you will be able to see the various Add-ins that are already selected in a drop down list.In the manage box you have to choose analysis Toolpak and then click on go button.

 

toolpak excel

Now the add-in dialog box will pop up.You have to choose the Analysis toolpak and then click ok.

 

How to do it ? :

First of all you can try this on a dummy sheet.  Click to download 

Here you can download a sheet consisting two data types,now you have to click on data tab and click on data Analysis now choose correlation. Then select the input range of the data in this example we are taking it from $A$1:$B$6 and then choose output range (place where the output will be shown) you have to select labels in first row option as we are selecting labels viz. hens and eggs along with numerical data.

correlation how to excel

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

 

This is the output

Now how to do it without Data Analysis Add-in

Choose the cell where output or result is to be shown,choose the formula tab and select the Function Library group and More Functions and Statistical now you have to select correlation and fill the arguments as Array 1 you have to input the data from column A1:A6 and for Array 2 you have to choose B1:B6

or a direct formula input in the desired output box ..i.e input following function in formula bar

=CORREL(A1:A6,B1:B6)

This function will also show same results.

correlation excel 2003

This method works easily with excel 2003,2007,2010 ,user can even try it with graph to verify it.