Start with a familiar calculation.
COVARIANCE.S calculates the sample covariance between two columns. MAKEARRAY repeats that calculation for every pair of variables.
Suppose C5:E10 contains observations in rows and variables in columns. The output has one row and one column for each variable, with the matching sample covariance at every intersection.
Let the indices do the work.
=LET(
Data_,C5:E10,
n_,COLUMNS(Data_),
MAKEARRAY(n_,n_,
LAMBDA(i_,j_,
COVARIANCE.S(
CHOOSECOLS(Data_,i_),
CHOOSECOLS(Data_,j_))
)
)
)LET names the data and its number of variables. MAKEARRAY supplies each output’s row and column index. CHOOSECOLS selects the corresponding input columns, and COVARIANCE.S performs the calculation.
Want the correlation matrix? Replace COVARIANCE.S with CORREL. The surrounding structure stays the same.
A calculation with a name.
The workbook packages the calculations as two LAMBDA functions, defined in Name Manager:
=fx_Covar(C5:E10)
=fx_Correl(C5:E10)The original LinkedIn post used fx_CovMat for its example. The downloadable workbook uses fx_Covar, alongside fx_Correl.
Four useful checks.
Symmetry
The covariance of A with B should equal the covariance of B with A.
The diagonal
A covariance matrix’s diagonal contains each variable’s sample variance.
Correlation
For nonconstant variables, diagonal correlations are one and all entries lie between −1 and 1.
Aligned observations
Use complete numeric observations for the same dates across every variable.
The LinkedIn post.
Read the post
Excel was launched in 1985. Forty one years later, it still does not have a built in worksheet function to compute an entire covariance or correlation matrix.
Of course, there are workarounds. Using matrix algebra, we can compute the covariance matrix by multiplying the transpose of the centered data matrix by the centered data matrix, then dividing by the number of observations minus one.
But there is a much simpler and more elegant solution using modern Excel functions: LET, MAKEARRAY, and LAMBDA.
Suppose Data (range C5:E10) contains our observations, with variables in columns. The entire sample covariance matrix can be computed with a single formula:
=LET(Data,C5:E10,n,COLUMNS(Data),MAKEARRAY(n,n,LAMBDA(i,j,COVARIANCE.S(CHOOSECOLS(Data,i),CHOOSECOLS(Data,j)))))That's it! One formula, and the entire covariance matrix automatically spills into the worksheet.
Want the correlation matrix instead? Simply replace COVARIANCE.S with CORREL.
Even better, we can turn this formula into a custom Excel function using LAMBDA.
Open Excel's Name Manager, define a new function called fx_CovMat, and assign it the formula:
=LAMBDA(Data,LET(n,COLUMNS(Data),MAKEARRAY(n,n,LAMBDA(i,j,COVARIANCE.S(CHOOSECOLS(Data,i),CHOOSECOLS(Data,j))))))From now on, calculating the covariance matrix is as simple as typing:
=fx_CovMat(C5:E10)No VBA, no macros, no complicated matrix manipulation. Just modern Excel. Sometimes, the most useful new Excel functions are the ones we create ourselves.
Function references
Function explanations and examples are linked to Exceljet.
Explore the calculation.
Download the covariance and correlation workbook, inspect the formulas, and use the functions with your own data.
Suggest an improvement or a new problem.
Spotted something that could be clearer, or a pricing, risk or portfolio problem you would like to see solved in Excel? Send it here: reader suggestions shape the next lessons.