Matrix in Excel (Create, Calculate Covariance, Multiply)

Get FREE Advanced Excel Exercises with Solutions!

In this article, you will learn how to calculate and work with a matrix in Excel. It includes how to create a matrix and how to perform matrix calculations like addition, subtraction, multiplication, transposition, inversion, and finding the determinant.

The matrix in Excel simplifies complex calculations and makes it easier to analyze data, do data modeling, and make decisions.

Inverting and finding the determinant of a matrix will allow you to solve certain mathematical problems efficiently.

If you want to do this by hand, it will take a lot of time; however, with Excel functions, you can find those values within a second.

Download Practice Workbook

You can download the Excel file and follow along with this article below.

Excel Matrices.xlsx

Table of Contents Expand

How to Create Matrix in Excel

We will create a matrix from our raw data that contains training information. Firstly, use the UNIQUE function in cell B16 to find the unique employee names.

=UNIQUE(B5:B12)

Secondly, type this formula in cell C15 to return the unique topics.

=TRANSPOSE(UNIQUE(C5:C12))

Then, we will combine the IFERROR, INDEX, and MATCH functions to return the matching dates to finish the matrix. So, type this formula in cell C16.
After that, fill in the formula for the rest of the cells.

=IFERROR(INDEX($D$5:$D$12,MATCH(1,INDEX(($B$5:$B$12=$B15)*($C$5:$C$12=C$14),),0)),"")

Finally, it will create a matrix in Excel.

Calculation of Matrix in Excel

1. Addition of Matrix

Apply this formula to calculate the addition of two matrices in Excel.

=B5+F5

2. Subtraction of Matrix

Use this formula to find the subtraction of two matrices in Excel.

=B5-F5

3. Multiplication of Matrix

We can apply the MMULT function to return the multiplication of two matrices. So, input this formula in cell B10.

=MMULT(B5:D7,F5:H7)

4. Transpose of Matrix

We can use the TRANSPOSE function to return the transpose of a matrix. Type this formula in cell B10.

=TRANSPOSE(B5:D7)

5. Inverse of Matrix in Excel

Insert the MINVERSE function in cell B10 to find the inverse of the matrix X.

=MINVERSE(B5:D7)

Note: Multiplying the inverse matrix with the original matrix will return the identity matrix. X (Original Matrix) * X^-1 (Inverse Matrix) = I (Identity Matrix).

6. Determinant of Square Matrix in Excel

Type this MDETERM function in cell B10 to find the determinant of Matrix X.

=MDETERM(B5:D7)

Things to Remember

When using the MDETERM, MINVERSE, and MMULT functions, all values need to be a number. Otherwise, there will be a #VALUE! error.
Multiplication of matrices is not commutative. That means, Matrix1 * Matrix2 <> Matrix2 * Matrix1.
The number of columns in Matrix1 needs to be equal to the number of rows in Matrix2 for multiplication.
The division of the matrix is unidentified.
If you use an older version of Excel, then you need to press Ctrl+Shift+Enter for array formulas.

Frequently Asked Questions

1. Is there a matrix template in Excel?

Yes, there is a template named “Competitor Analysis” that is a matrix template in Excel.

2. How do I name a matrix in Excel?

You can select the range, and using the Name Range feature, you can name a matrix.

3. How can I apply conditional formatting to a matrix in Excel?

Select the matrix, then from the Home tab, select the Conditional Formatting feature. From there, you can select various options to apply the feature to a matrix.

Conclusion

In this article, we have talked about the matrix in Excel. If you have any questions or suggestions about this, please feel free to comment below, and the ExcelDemy team will try to solve the issue as soon as possible. Thanks for reading, and keep excelling!

Matrix in Excel: Knowledge Hub

<< Go Back to | Excel for Math | Learn Excel |