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 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.

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)