Include Top

Home | Login | Live Help is Live Help

Included Menu

How Do I create a Correlation Matrix in Excel Using SigmaXL?

Correlation Matrix

The correlation matrix complements the scatterplot matrix by quantifying the degree of association. The following table shows the approximate relationship between r, R-squared, and degree of association:

Pearson Correlation Coefficient (r) R-Squared (%) Degree of association
0.9 <= |r| <= 1 > 80% Strong
0.7 <= |r| <= 0.9 50% to 80% Moderate
|r| < 0.7 < 50% Weak
Pearson Probability, p > 0.05   None
  1. Open Customer Data.xls. Click Sheet 1 tab (or press F4 to activate last worksheet). Click SigmaXL > Statistical Tools > Correlation Matrix. Ensure that entire data table is selected. If not, check Use Entire Data Table. Click Next.
  2. Select the variable Avg No. of orders per month; shift-click on Staff Knowledge and click Numeric Data Variables (Y) >> as shown:

    Correlation Matrix Variables

  1. Click OK. Resulting Correlation Matrix is shown:

  2. Correlation Matrix
    Correlations highlighted in red are considered significant (p-values < .05). The corresponding correlation coefficients above the p-values are also highlighted in red. (Compare these to the Scatterplot Matrix.)

    Note that Spearmanís Rank Correlation complements Pearsonís Correlation, in that it provides a robust measure of association. Spearmanís rank is based on correlated ranks, which are not sensitive to outliers.

Click here to download a Free 30-Day Trial.