Principal Component Analysis in Excel: Standardise, Covariance, Eigenvectors & Scores

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Compute the mean and sample standard deviation of four city indicators, turn every value into a z-score, count the unusual cities and see why unscaled data would let one variable dominate.
• Build the 4 × 4 covariance matrix of the raw data and the correlation matrix of the z-scores with one MMULT each, link the two with r = cov ÷ (sx · sy) and colour the correlations.
• Multiply a start vector by R again and again, normalise it to length 1 each time and watch it settle on the first principal direction; the stretch factor settles on the first eigenvalue.
• Check that R only stretches v₁, compute the eigenvalue λ₁ with the Rayleigh quotient, the total variance as the trace of R and the share of variance PC1 explains.
• Remove PC1 from R (deflation), run the power iteration on what is left to get v₂, then compute λ₂ and the variance PC1 and PC2 explain together.
• Turn six eigenvalues into explained and cumulative variance shares, apply the 80% rule and the Kaiser rule, and draw a scree plot to look for the elbow.
• Project every city onto PC1 and PC2, compute the loadings that link components back to the indicators and map all ten cities in a scatter chart.
• Rebuild the z-scores from one component (score × direction), measure the squared reconstruction error, express it as variance lost and compare k = 1 with k = 2 using MMULT.