Microsoft Excel: Pearson's Correlation

The correlation is a relationship between two variables. Consider the following hypothetical data organized in Microsoft Excel.

To perform univariate correlation, the X variable should be arranged in a column and the Y variable in a column to its right.

To plot these two variables, first select both the columns in the insert tab, find charts, and select a scatter plot.

Other elements like trendlines can also be added to the chart designs.

Assuming that the data are from normally distributed populations, Pearson's correlation coefficient — r — is calculated using the function CORREL the selected for data.

Another function, PEARSON, also returns the same value.

These functions measure the strength and direction of the linear relationship between two variables. In this case, it is 0.985, suggesting a strong and positive correlation.

The function RSQ returns the squared value of r. This value is the coefficient of determination that measures the proportion of the variance in the dependent variable that is predictable from the independent variable.