Include Top

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

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.xlsx. 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 or departures from normality.

    An automatic normality check is applied, utilizing the powerful Doornik-Hansen bivariate normality test. A yellow highlight recommends Pearson or Spearman correlations be used (but only if it is significant). Pearson is highlighted if the data are bivariate normal, otherwise Spearman is highlighted. Always review the data graphically with the scatterplot matrix as well.

Define, Measure, Analyze, Improve, Control

Lean Six Sigma Software Excel Add-in

Simulate, Optimize,

Lean Six Sigma Software Excel Add-in

Web Demos

Our CTO and Co-Founder, John Noguera, regularly hosts free Web Demos featuring SigmaXL and DiscoverSim
Click here to view some now!

Contact Us

Ph: 1.888.SigmaXL (744.6295)