MMULT Excel Function for Matrix Multiplication

Since a worksheet is essentially a gigantic matrix, it’s no surprise that matrix multiplication in Excel is super easy – we just need to use the MMULT Excel function.

Matrix Multiplication with the MMULT Excel function

You can multiply matrices in Excel thanks to the MMULT function. This array function returns the product of two matrices entered in a worksheet.

The syntax for the function is:

MMULT(array1,array2)

where array1 and array2 are the arrays or matrices to be multiplied.

The result of the MMULT function is an array with a number of rows that’s the same as array1 and a number of columns that is the same as array2.

If you are using Excel 365, MMULT is a dynamic array function. This means that you can enter a formula using MMULT into a single cell, and Excel will automatically expand the range to fit the result of the function.

If you are NOT using Excel 365, MMULT must be entered as an array function by selecting the appropriate number of cells for the result array and typing CTRL+SHIFT+ENTER.

Matrix Multiplication Rules

To perform matrix multiplication in Excel effectively, it’s helpful to remember how matrix multiplication works in the first place. So, let’s say we have two matrices, A and B, as shown below:

two matrices

The product of these two matrices (let’s call it C), is found by multiplying the entries in the first row of column A by the entries in the first column of B and summing them together. This is also known as the dot product. This single value becomes the entry in the first row and first column of matrix C.

See also  Create From Selection Excel Technique: Name Multiple Cells Quickly

matrix multiplication with dot product

We continue doing this until we’ve found the dot product for each row and column combination.

Since we multiply the rows of matrix A by the columns of matrix B, the resulting matrix C will have a size of 2 x 2. Or more generally, the matrix product has the same number of rows as matrix A, and the same number of columns as matrix B.

Because of the way matrix multiplication works, it’s also important to remember that we can only multiply two matrices if the number of rows in B matches the number of columns in A.

Got it?

If not, that’s ok. Hopefully a few examples will clear things up. 🙂

Excel Matrix Multiplication Examples

Matrix Multiplication as an Array Function (All Versions of Excel)

Let’s take the matrices from above and find the product using matrix multiplication in Excel with the MMULT function:

two matrices in excel

First, let’s find C, the product of AB.

Since MMULT is an array function, it will return values to more than one cell.

We know that the result is going to be a 2×2 matrix because the first matrix, A, has two rows and the second matrix, B, has two columns.

So, if we were working in Excel 365 or any previous version of Excel, we select an area on the worksheet 2 cells wide by 2 cells high:

select output cells matrix multiplication in excel

Next, with the area still selected, type the array formula and select the arguments (I assigned names to the matrices A and B):

mmult excel matrix multiplication

Finally, type CTRL+SHIFT+ENTER (because it’s an array formula) to return the resulting matrix product:

MMULT excel result

Matrix Multiplication as a Dynamic Array Function (Excel 365 Only)

In Excel 365, it’s not necessary to select the correct number of return cells or type CTRL+SHIFT+ENTER. Simply enter the array formula in the upper left most cell of the array and Excel will dynamically fill all the resulting values:

See also  How to Enable the Excel Solver Add-In

MMULT dynamic array excel 365

Other MMULT Examples in Excel

What if, instead of calculating matrix C, by multiplying matrix A times B, we calculated a different matrix D, by multiplying matrix B times A?

Since the first matrix, B, has 3 rows, and the second matrix, A, has 3 columns, matrix D would have 3 rows and 3 columns. So, we would start with a selection 3 cells wide and high:

matrix multiplication with MMULT in Excel

Then, type in the formula for MMULT, selecting B as array1 and A as array2…

mmult excel function

… and type CTRL+SHIFT+ENTER to obtain matrix D:

matrix D

The MMULT function also works for multiplying a matrix (A) by an array (x). Of course, the rule still stands that the number of rows in x must match the number of columns in A.

The result is an array, F, that has 1 column and the same number of rows as A:

mmult excel calculation

MMULT Excel Errors (Why is MMULT not working in Excel?)

There are a few different ways that you can get errors in Excel with the MMULT function:

MMULT #VALUE! Error

A #VALUE! error will occur when the number of columns in array 1 (A below) is not the same as the number of rows in array2 (x below).

mmult value error

To fix this would require that array x has three rows, so it probably means that one of the values from array x is missing.

If you are not using Excel 365 and MMULT is not entered as an array formula by typing CTRL+SHIFT+ENTER, it will also return a #VALUE! error to a single cell:

mmult error array formula

Finally, a #VALUE! Error will also occur if any of the elements in either array1 or array2 do not contain numbers.

value error with mmult in excel

MMULT Returns a Single Value

If you are not using Excel 365 and MMULT returns a single value, it’s because only a single cell was chosen for the resulting array. In earlier versions of Excel, it’s up to the user to select the appropriate number of cells for Excel to return a result to. If a single cell is selected, MMULT will perform the matrix multiplication and return the first value in the result array to that cell.

See also  Nonlinear Curve Fitting in Excel Using Charts

It’s also possible for this behavior to occur in Excel 365 if a single cell is selected for the result and the MMULT function is entered as an array formula by typing CTRL+SHIFT+ENTER.

MMULT Returns Too Many Values

Although not always an error, MMULT can return some surprising results when the output area for the function is larger than required.

Adding extra columns causes the results to be duplicated:

mmult returns too many variables

MMULT #N/A! Error

Adding extra rows to the MMULT array causes an #N/A! error to be displayed, although it’s only displayed in the extra cell:

mmult NA error in excel

Matrix Multiplication Limits in Excel

There are no software-imposed limits to the size of matrix that can be multiplied in current versions of Excel. Basically, you can multiply matrices as large as you want provided you have enough RAM in your computer.

However, if you’re still using Excel 2003 or earlier, you’ll be restricted to an output of 5046 cells when using the MMULT function (roughly a 71×71 matrix).

Even if you are in newer versions of Excel, there is one caveat: you cannot create an array that fills an entire column of a worksheet. Since there are 1,048,576 rows in a worksheet in Microsoft Excel 2007 and later, I guess this does constitute a limit on matrix multiplication in Excel.

This article from Microsoft support has some helpful information on the limitations of Excel when working with arrays.

Scroll to Top