MMULT() Excel: How to Do Matrix Multiplication in Excel

You might not use matrix multiplication every day in Excel, or even think about how Excel handles linear algebra operations, but when the need for it does come up, MMULT() is the central function to know.

In this article, I'll show you how MMULT() works and I hope you come to appreciate how it's surprisingly helpful for calculating weighted sums, modeling financial outcomes, or working with multidimensional data.

Why Use Excel MMULT()?

Let’s start with the big picture. The MMULT() function performs matrix multiplication, which is a math operation where you multiply rows of one matrix by columns of another. It’s especially helpful when you're working with things like:

• Weighted averages
• Financial forecasting
• Regression models

In Excel, MMULT() can help automate and speed up this process without needing separate tools like Python or R. While Python and R are good for this kind of thing, if you are working in Excel, it might be more time-consuming to have to switch back-and-forth.

Excel MMULT() Syntax and Arguments

Here are the MMULT() required arguments. As you'd expect, it takes in two arrays.

=MMULT(array1, array2)

• array1: The first matrix (range of cells)

• array2: The second matrix (range of cells)

The number of columns in array1 must match the number of rows in array2. If this condition isn't met, Excel won’t perform the multiplication and will return an error.

Important note: If you’re using Excel 365 or Excel 2021 (or later), just hit Enter like any normal formula. But if you’re on an older version, you’ll need to use a special key combo:

1. Select the output range (make sure it’s sized correctly!).

2. Type in your MMULT() formula.

3. Press Ctrl + Shift + Enter to enter it as an array formula.

When you do this right, Excel surrounds your formula with curly braces {} to show it’s been entered as an array formula.

MMULT() in Action: A Real Example

Let’s bring this to life with an example.

Say you have the following two matrices:

If you want to multiply these two matrices, use:

=MMULT(A1:C2, E1:G3)

Here are the operations:

• C[1][1] = 14 = (1×1) + (2×2) + (3×3) = 1 + 4 + 9 = 14
• C[1][2] = 58 = (1×7) + (2×9) + (3×11) = 7 + 18 + 33 = 58
• C[1][3] = 64 = (1×8) + (2×10) + (3×12) = 8 + 20 + 36 = 64
• C[2][1] = 32 = (4×1) + (5×2) + (6×3) = 4 + 10 + 18 = 32
• C[2][2] = 139 = (4×7) + (5×9) + (6×11) = 28 + 45 + 66 = 139
• C[2][3] = 154 = (4×8) + (5×10) + (6×12) = 32 + 50 + 72 = 154

Once entered correctly, Excel will return a new matrix based on the product. Each number in the resulting matrix is the sum of products from rows of array1 and columns of array2. We didn't have to do all the row and column operations that I just showed above; Excel takes care of all the heavy lifting.

Common Errors and How to Fix Them

Working with MMULT() can trip you up if you’re not careful. Here are some common issues:

• #VALUE! error: This happens if your arrays have non-numeric values, or if their dimensions are incompatible.
• Incorrect result size: The size of your output range must match the dimensions of the resulting matrix. If not, Excel might only return part of the result, or I else it might in other cases return nothing at all.
• Forgetting Ctrl + Shift + Enter: In older versions of Excel, this is a common pitfall. Without this step, the function won’t return an array output.

Always double-check the size of your input and output ranges before entering the formula.

Visualizing MMULT() Results

Here is one idea to help you better see the result: Use color scales to show high vs. low values in your resulting matrix. This can make large or complex results easier to interpret.

Conclusion

The MMULT() function is a useful function in Excel for handling matrix multiplication which is going to relevant in things like regression and financial analysis. Once you know how to size your arrays and enter the function correctly, it can save you time and reduce errors.

Check out our courses on Data Analysis in Excel and Advanced Excel Functions to keep building your expertise. Financial Modeling in Excel is another great option which includes interesting things like sensitivity analysis and forecasting.