How to Calculate Inverse Matrix in Excel (3 Different Ways)

An inverse matrix in Excel is a useful tool for solving mathematical and data problems that involve multiple variables. It works like dividing numbers in regular math. For example, just as the inverse of 5 is 1/5, the inverse of a matrix helps you divide by that matrix during calculations.

Excel makes it easy to find an inverse matrix using the MINVERSE function. You can get the result in just a few steps without doing long calculations by hand.

In this article, you will learn how to calculate an inverse matrix in Excel step by step using simple methods.

Key Takeaways

Here’s how to calculate inverse matrix using the MINVERSE function in Excel:

➤ Open your dataset in Excel.
➤ Select the range where you want to display the inverse matrix. Since our original matrix is in A2:C4, select a 3×3 block such as E2:G4.
➤ In the formula bar, type the following formula:
=MINVERSE(A2:C4)
➤ Press Enter.
➤ The inverse of the matrix will appear in the selected range.

What is Inverse Matrix in Mathematics?

An inverse matrix in Excel is a matrix that gives the identity matrix when multiplied by the original one. The identity matrix has 1s on the diagonal and 0s in all other places.

In simple words, if you have a square matrix A, its inverse is written as A⁻¹, and they follow this rule.

A × A⁻¹ = I

Here, I is the identity matrix.

Let’s create a simple 3×3 numeric matrix to work with. In Excel, set up the following in cells A1 to C4. Cells A2:C4 hold the matrix A.

We will compute its inverse, place it perhaps in E2:G4, and then multiply A × A⁻¹ to see if we get the identity matrix.

You can label cell D1 as Inverse of A and label cell H1 as A × A⁻¹ (Check).

The MINVERSE function is the easiest and most common way to find the inverse of a matrix in Excel. It works only with square matrices (the number of rows and columns must be the same). This function returns the inverse matrix directly without needing any manual calculation.

Here’s how to do it:

➤ Open your dataset in Excel.
➤ Select the range where you want to display the inverse matrix. Since our original matrix is in A2:C4, select a 3×3 block such as E2:G4.
➤ In the formula bar, type the following formula:

➤ Press Enter if you are using Excel 365 or Excel 2021. If you are using an older version of Excel, press Ctrl + Shift + Enter to apply it as an array formula.
➤ The inverse of the matrix will appear in the selected range. If the matrix cannot be inverted (because its determinant is 0), Excel will show a #NUM! Error.

After finding the inverse matrix with the MINVERSE function, it is a good idea to check if the result is correct. You can do this easily using the MMULT function in Excel. This function multiplies two matrices and helps confirm that the inverse is accurate.

If the inverse is correct, multiplying the original matrix by its inverse. It should give you the identity matrix such as 1s on the diagonal and 0s elsewhere.

Here’s how to do it:

➤ Open your worksheet with the original matrix in A2:C4 and its inverse in E2:G4.
➤ Select a 3×3 block such as I2:K4 to display the result.
➤ In the formula bar, type the following formula:

➤ Press Enter if you are using Excel 365 or Excel 2021. If you are using an older version, press Ctrl + Shift + Enter to apply it as an array formula.
➤ Excel will display the result of the multiplication.
➤ You may see very small numbers like -1.11E-16 or 2.78E-17 instead of exact zeros. These are normal and can be treated as zeros. The result confirms that your inverse matrix is correct.

If you need to calculate inverse matrices frequently, you can use a VBA custom function. This allows you to get the inverse with a simple formula, without manually selecting ranges or using array formulas every time.

Here’s how to do it:

➤ Press Alt + F11 to open the VBA editor.
➤ Click Insert >> Module.
➤ Copy and paste the following code into the module:

Function InverseMatrix(rng As Range) As Variant
    Dim matrix As Variant
    matrix = rng.Value
    InverseMatrix = WorksheetFunction.MInverse(matrix)
End Function

➤ Close the VBA editor and return to your worksheet.
➤ Select the range where you want the inverse matrix to appear. For example, E2:G4.
➤ In the formula bar, type:

➤ Press Ctrl + Shift + Enter if you are using an older version of Excel. In newer versions, only press Enter .
➤ Excel will display the inverse matrix in the selected range.

➤ To verify the result, use the MMULT function to multiply the original matrix by the VBA-generated inverse.
➤ Select a 3×3 block and type such as I2:K4:

➤ Press Enter or Ctrl + Shift + Enter in older versions.
➤ The result should be close to the identity matrix, with very small numbers like 1E-16 treated as zeros. This confirms that the inverse calculated by VBA is correct.

Downloadable Resources

Get our practice workbook or necessary files that we have worked with to prepare this article.

Wrapping Up

Calculating an inverse matrix in Excel helps solve equations and analyze data easily. You can quickly find the inverse of any square matrix and check your result by multiplying it with the original matrix to get the identity matrix.

Excel also handles errors and small rounding differences automatically. Using these steps, you can work with matrices confidently and make your calculations faster and more accurate.