Learning how to calculate correlation or dependence in Excel is a very simple but useful skill. Correlation calculation is a great way to determine if two sets of data are related in other words if one variable changes, does the second variable also change? It is also used to determine how much two sets of data are related to each other such as the relationship between price and demand. In other words, if the price for a product goes up, the demand for the product goes down. It is important to note that correlation or dependence does not imply causality. There may be a causal relationship between the two factors, but correlation in itself does not indicate that this is so.
A measure of the amount of correlation between two factors is correlation coefficient. If the correlation coefficient has a value of 1 or close to one, it indicates that the two data sets are highly correlated. If the value is 0 or very close to 0, the two factors are not correlated. If the correlation coefficient is equal to -1 or close to the value, the two factors negatively correlated. In other words, if one variable goes up the other one goes down. This number can also be used to evaluate the relative correlation between one set of factors versus another set of factors.

Things You Will Need

Microsoft Excel
Two data sets

Step 1

Enter corrolcation data Enter the data for the first data set in one column and the second data set into a second column. Each data value in the first column must have a corresponding data value in the second column.

Step 2

set corrolcation function Enter the function "=CORREL" in a separate cell. The function requires two sets of data to perform the correlation calculation.

Step 3

Enter first corrolcation data Enter the first array of data by selecting the values for the first set. Be sure to select all of the data in the first set.

Step 4

Enter second corrolcation data Type a "," in the function and select the data from the second set. Make sure that you end the function with a ")".

Step 5

corrolation coifficient Push the return or tab button and the resulting number is the correlation coefficient. Array

Tips & Warnings

  • If the "CORREL" function is not recognized by Excel, it is because not all of the statistical functions have been loaded. To load the function, go to tools and load click "more functions", choose "statistical", then "CORREL".
  • Note that correlations work well for "straight line" relationships, but don't work as well with most curves. To determine correlation between two curves, you will need to perform some linear regression analysis.