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.

Matrix in Excel

Download Practice Workbook

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

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.


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


Formula to Find Unique Values

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


Formula to Return Matching Date Data

  • Finally, it will create a matrix in Excel.

Final Output of Creating Matrix in Excel

Calculation of Matrix in Excel

1. Addition of Matrix

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


Addition of Matrix in Excel

2. Subtraction of Matrix

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


Subtraction of Matrix in Excel

3. Multiplication of Matrix

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


Finding Multiplication

4. Transpose of Matrix

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


Finding Transpose Value

5. Inverse of Matrix in Excel

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


Finding Inverse of Matrix

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.


Calculating Determinant of Matrix in Excel

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.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF