A single number can tell you whether a system of equations has a clean solution. That number is the determinant. In advanced models — engineering, economics, statistics — you often need to know if a matrix is well-behaved before you trust its results. Calculating a determinant by hand is tedious and easy to get wrong. Excel does it in one step. The MDETERM function takes a square matrix and returns its determinant instantly.
This guide explains what a determinant means in plain terms. Then it shows how MDETERM works, why the matrix must be square, and how the result guides real decisions. Six worked examples and a full troubleshooting section follow.
What a Determinant Tells You
The determinant is a single value calculated from a square matrix. In short, it summarises key properties of that matrix in one number. This one number carries a lot of meaning. It reflects how the rows and columns relate. Its most useful signal is simple. When the determinant is not zero, the matrix has an inverse and the linked equations have one unique solution. When it is zero, the matrix is "singular" and no unique solution exists. The infographic below shows the calculation for the simplest case.
For a 2x2 matrix, the rule is easy to see. You multiply the main diagonal, then subtract the other diagonal. Larger matrices follow a more involved pattern. Each size has its own expansion method. Fortunately, MDETERM handles all sizes for you. You never need to memorise the larger rules. The function applies them behind the scenes.
Why the Matrix Must Be Square
A determinant only exists for a square matrix. That means the number of rows must equal the number of columns. A 3x3 or 4x4 block works. However, a 2x3 block does not. If you give MDETERM a non-square range, it returns an error. This rule is strict and has no exceptions. A rectangular matrix simply has no determinant. Therefore, always check the shape first.
The Syntax
MDETERM needs only one argument. As a result, it is quick to write. Unlike some matrix functions, it returns a single number, not a spilled array.
Example 1: A Simple 2x2 Determinant
Start with the smallest case. A 2x2 matrix makes the logic clear. Once you see it here, larger matrices feel less mysterious.
Example 2: A 3x3 Determinant
Real models rarely stop at 2x2. A 3x3 matrix is very common. By hand, the 3x3 rule takes several steps. It involves multiplying and subtracting across all three rows. Mistakes creep in easily. With MDETERM, the size makes no difference. You simply point it at a 3x3 range. The function does every step internally. As a result, you save time and avoid arithmetic slips.
Example 3: Test Whether a Matrix Is Invertible
This is the most practical use of MDETERM. Before you invert a matrix or solve a system, you check the determinant. If it is zero, no unique solution exists. Consequently, an inverse attempt will fail. A quick test saves you from a confusing error later. It is a small habit with a big payoff. Experienced modellers run this check by reflex.
Example 4: Determinant as an Area Scale Factor
A determinant has a geometric meaning too. It measures how a transformation scales area or volume. For instance, a 2x2 matrix transforms a shape. The absolute value of its determinant is the area scaling factor. This idea appears in graphics, physics, and engineering models.
Example 5: Check a Determinant Across Scenarios
Models often test many input sets. You may want the determinant for several versions of a matrix. MDETERM fits neatly into this. Simply place it beside each scenario. Then compare the results at a glance.
Here Variant A stands out at once. Its determinant is zero. Therefore, it cannot be solved uniquely. The other two are safe to work with. This side-by-side view makes bad inputs obvious. You can catch a problem scenario before it breaks a model.
Example 6: Combine MDETERM with MINVERSE and MMULT
MDETERM rarely works alone. It usually pairs with its sibling functions. Together they solve linear systems. First, MDETERM confirms a solution exists. Then MINVERSE and MMULT compute it. This trio is the core of matrix algebra in Excel.
Example 7: Spot Redundant Data in a Model
MDETERM has a practical role in data analysis. A determinant near zero warns of a problem. It often means two variables carry almost the same information. Analysts call this multicollinearity. Consequently, a near-zero determinant is a red flag worth checking before you build a regression model.
Troubleshooting MDETERM
All three problems below are the most common. Each has a clear cause and a quick fix.
You get a #VALUE! error
This error has two usual causes. First, the range may not be square. MDETERM needs equal rows and columns, so a 2x3 block fails. Count the shape and fix it. Second, the range may contain text or blank cells. Every cell must hold a number. Look for stray labels, spaces, or gaps in the block. Once the range is square and fully numeric, the error clears. Replacing blanks with zeros is often the fastest fix.
The result is a tiny number instead of exactly zero
Sometimes a determinant that should be zero shows as a very small value, like 1E-15. This is normal. It comes from floating-point rounding inside the calculation. The true answer is zero, but tiny errors accumulate. To handle this, do not test for an exact zero. Instead, round the result first, or compare against a small tolerance. For example, treat any value below 0.0000001 in size as effectively zero. This keeps your invertibility checks reliable.
The determinant looks wrong for my data
If the number seems off, check the range boundaries first. It is easy to include a header row or an extra column by mistake. That changes the matrix MDETERM reads. Make sure the selected range covers only the numeric matrix, with no labels. Also confirm the cells hold real numbers, not text that looks like numbers. A value stored as text can distort the result. Correct the range and the data type, and the determinant will match your expectation.
Frequently Asked Questions
- What does the MDETERM function do in Excel?MDETERM calculates the determinant of a square matrix and returns it as a single number. The determinant summarises important properties of the matrix. Most usefully, it tells you whether the matrix can be inverted. A non-zero determinant means an inverse exists and the related equations have one unique solution. A zero determinant means the matrix is singular, so no unique solution exists. You write it as =MDETERM(range), where the range is a square block of numbers such as A1:C3. It handles any square size, doing the full calculation internally.
- Why does MDETERM return a #VALUE! error?There are two common causes. First, the range is not square — MDETERM needs the same number of rows and columns, so a 2x3 range triggers the error. Count the shape and correct it. Second, the range contains non-numeric cells such as text, labels, or blanks. Every cell in the matrix must hold a number. Check for stray text, spaces, or empty cells inside the block. Fixing the shape and replacing any blanks with numbers usually clears the error. Numbers stored as text can also cause problems, so convert them to real numbers if needed.
- How do I know if a matrix is invertible using MDETERM?Calculate the determinant with MDETERM and check whether it is zero. If the result is not zero, the matrix is invertible and its equations have a unique solution. If the result is zero, the matrix is singular and cannot be inverted. A handy formula is =IF(MDETERM(range)=0,"Singular","Invertible"). Run this test before attempting MINVERSE, because inverting a singular matrix fails. Note that floating-point rounding can make a true zero appear as a tiny value, so compare against a small tolerance rather than an exact zero for the most reliable check.
- Can MDETERM handle large matrices?Yes. MDETERM works on square matrices of many sizes, from 2x2 up to fairly large blocks, and it does the full calculation for you. The only firm rule is that the matrix must be square, with equal rows and columns. For very large matrices, be aware that floating-point rounding can slightly affect the result, especially when the true determinant is close to zero. In those cases, round the answer or compare it against a small tolerance. For most business and engineering models, which use modest matrix sizes, MDETERM returns accurate results quickly and reliably.