Use Correlation Test of MS Excel

Data Analysis of Excel has a function to determine the relationship between two variables.  Result of this function is coefficient of correlation which shows the strengh or weakness of a relationship. The value of coefficient ranges between -1.0 to +1.0

We usually define correlation as a measure of relationship between two mathematical variables or measured data values. Statisticians are familiar with Pearson product-moment correlation coefficient (PMCC) which measure the correlation between two variables X and Y.

We can use Microsoft Excel to get more insight about correlation. Use some simple data first before move to more complicated data.

How to run the correlation test?

  1. Prepare two data sets to be tested (eg. income and sales). The data could be either in two rows or two columns.  Place the header label in first row if it in column.
  2. Select Data Analysis at Data tab of the Ribbon
  3. Excel will provide a correlation wizard.  Put the range of selected cells at the Input rnge.
  4. At the Output options, choose where the ouput test will be displayed.
  5. Press OK