Principal Component Analysis in Excel: Standardise, Covariance, Eigenvectors & Scores
PCA from scratch with formulas: z-scores, covariance and correlation matrices with MMULT, the first eigenvector by power iteration, eigenvalues and explained variance, deflation for PC2, a scree plot, scores and loadings, and reconstruction error: 8 graded labs on a city dataset.
The first lesson is free — no account needed.
About this course
A principal component analysis course that builds PCA from scratch with formulas on a dataset of ten fictional cities for one regional planning office, Larkspur, for data analysts, statistics and data science students and anyone who has used PCA as a black box. You standardise four indicators into z-scores, build the covariance and correlation matrices with one MMULT each, find the first eigenvector by power iteration and its eigenvalue with the Rayleigh quotient, measure the variance PC1 explains, deflate the matrix to get the second component, choose the number of components with a scree plot, the 80% rule and the Kaiser rule, compute scores and loadings and map the cities in a scatter chart, and measure the reconstruction error of one and two components. No add-in and no Python needed. The first lab is free.
Lessons
- Standardising Variables: z-Scores before PCAFree
- Covariance & Correlation Matrices with MMULT and TRANSPOSE Pro
- Power Iteration: the First Eigenvector with Repeated MMULT Pro
- Eigenvalue & Explained Variance: How Much Does PC1 Capture? Pro
- The Second Component: Deflation and Another Power Iteration Pro
- Scree Plot & Cumulative Variance: How Many Components? (with a Chart) Pro
- Component Scores & Loadings: Placing and Naming the Cities (with a Chart) Pro
- Reconstruction Error: What You Lose with k Components Pro