How to Use the MDETERM and MUNIT Functions in Microsoft Excel for Matrix Operations

Matrix algebra is a mathematical foundation for linear regression, economic input-output models, 3D graphics transformations, engineering stress analysis, and solving systems of simultaneous linear equations. While functions like MMULT and MINVERSE handle multiplication and inversion, two essential companion functions complete Excel’s matrix capabilities: MDETERM (which computes the scalar determinant of a square matrix) and MUNIT (which generates an identity matrix of specified dimensions).

This tutorial explains the mathematical properties, syntax, and step-by-step applications of MDETERM and MUNIT in Microsoft Excel, including testing for matrix invertibility and solving systems of equations using Cramer’s Rule.

Understanding Determinants and Identity Matrices

Before implementing Excel formulas, review the algebraic significance of these operations:

• Matrix Determinant (MDETERM): A single scalar number calculated from a square matrix (such as 2×2, 3×3, or NxN). Crucially, a matrix is only invertible if its determinant is non-zero (MDETERM ≠ 0). If a determinant equals zero, the matrix is “singular”, meaning no unique solution exists for the underlying system of equations.
• Identity Matrix (MUNIT): The matrix equivalent of the number 1. An identity matrix is a square array populated with 1s along the main diagonal (from top-left to bottom-right) and 0s everywhere else. Multiplying any matrix A by an identity matrix I leaves matrix A unchanged (A × I = A).

Syntax and Parameters of MDETERM and MUNIT

MDETERM Syntax

=MDETERM(array)

array is a numeric range containing an equal number of rows and columns (e.g. A1:C3). MDETERM returns a single scalar numeric value.

MUNIT Syntax

=MUNIT(dimension)

dimension is a positive integer specifying the size of the square unit matrix (e.g. entering 3 generates a 3×3 identity matrix). In modern Excel with dynamic arrays, MUNIT automatically spills into adjacent rows and columns.

Formula Input	Input Matrix Dimensions	Mathematical Output Result
=MDETERM(A1:B2)	2×2 square matrix	Evaluates scalar determinant: (A1 × B2) - (B1 × A2).
=MDETERM(A1:C3)	3×3 square matrix	Evaluates triple diagonal cross-products using cofactor expansion.
=MUNIT(3)	Integer: 3	Spills a 3×3 array containing 1s on the diagonal and 0s elsewhere.

Step-by-Step Practical Demonstration: Testing Invertibility and Cramer’s Rule

Consider the following system of three linear equations with three unknown variables (x, y, z):

2x + 1y - 1z = 8
-3x - 1y + 2z = -11
-2x + 1y + 2z = -3

Step 1: Set Up Coefficient Matrix in Excel

Enter the coefficients into cells A1:C3:

• Row 1 (A1:C1): 2, 1, -1
• Row 2 (A2:C2): -3, -1, 2
• Row 3 (A3:C3): -2, 1, 2

Enter the constant results vector (8, -11, -3) into cells E1:E3.

Step 2: Calculate Determinant of the Coefficient Matrix

To confirm that this system of equations has a unique solution, calculate the determinant in cell A5:

=MDETERM(A1:C3)

Excel computes the determinant and returns -1. Because the result is non-zero, the matrix is mathematically non-singular and uniquely solvable.

Step 3: Generating an Identity Matrix with MUNIT

To generate an identity matrix to verify matrix transformations or diagonalize models, select an empty cell (e.g. G1) and type:

=MUNIT(3)

Excel immediately spills a 3-row by 3-column identity grid:

1  0  0
0  1  0
0  0  1

Troubleshooting Common Matrix Function Errors

If your matrix formulas return errors, verify these standard algebraic constraints:

• #VALUE! Error in MDETERM: Triggered when the referenced cell range is not an exact square (e.g. entering a 2×3 or 3×4 rectangular range). Determinants are only mathematically defined for square matrices with identical row and column counts. This error also occurs if any cell within the matrix contains non-numeric text.
• #VALUE! Error in MUNIT: Occurs if dimension is less than or equal to 0, or contains text. Dimension must be an integer ≥ 1.
• #REF! Error in MUNIT: Appears when the dynamic array cannot spill because existing data blocks the required grid cells. Clear the adjacent cells to the right and below the formula.