How to Perform Matrix Multiplication in Excel Using the MMULT Function

Before attempting any calculation, it is paramount to understand the mathematical constraints governing matrix multiplication. For two input arrays—designated as array1 (Matrix A) and array2 (Matrix B)—to be compatible for multiplication, the number of columns in Matrix A must be identical to the number of rows in Matrix B. If this dimensional requirement is not met, the MMULT function will return an error, signaling an impossible operation. Furthermore, the resulting product matrix will inherit the number of rows from Matrix A and the number of columns from Matrix B.

The MMULT function is a powerful implementation of the linear algebra operation, designed to handle cell ranges representing the input arrays. Once the calculation is performed, the output is returned as an array that spans multiple cells. In modern versions of Excel, this function benefits from Dynamic Array functionality, meaning the result automatically spills into the required number of cells. For legacy versions of Excel, users must enter the formula as a CSE (Ctrl+Shift+Enter) array formula after selecting the destination range for the output.

This guide will explore the precise application of the MMULT function through practical examples involving matrices of varying dimensions. We will examine the multiplication process for square and rectangular matrices, providing clear steps and visual confirmations of the results.

Understanding the MMULT Syntax and Prerequisites

The core functionality of the MMULT function relies on correctly identifying and inputting the ranges corresponding to the two matrices being multiplied. The syntax is elegantly simple but demands strict adherence to dimensional constraints. If Matrix A is an M x N matrix (M rows, N columns) and Matrix B is a P x Q matrix (P rows, Q columns), multiplication is only possible if N equals P. If this condition holds, the resulting product matrix will have the dimensions M x Q.

This function uses the following basic syntax:

MMULT(array1, array2)

where:

• array1, array2: These are the ranges or cell references that represent the two matrices intended for multiplication. These ranges must contain numerical values; non-numeric cells within the range will cause the function to return a #VALUE! error.

Furthermore, both input arrays must be rectangular arrays, meaning they must occupy a contiguous block of cells (e.g., A1:B2 or D5:F8). Using disjointed or incorrectly shaped ranges will lead to calculation errors or unexpected results. Proper selection of the input ranges is the first critical step toward successful matrix multiplication in Excel.

Example 1: (2×2) by (2×2) Matrix Multiplication

Our first example demonstrates the multiplication of two square matrices, specifically a (2×2) matrix multiplied by another (2×2) matrix. This is the simplest case where the dimensional compatibility is inherently guaranteed, as the number of columns in the first matrix (2) equals the number of rows in the second matrix (2). The resulting product will also be a (2×2) matrix.

The following screenshot illustrates the setup where Matrix A occupies range A2:B3 and Matrix B occupies range D2:E3:

To perform this operation, we selected the four cells designated for the result (in this case, G2:H3) and entered the necessary MMULT function formula into the starting cell, G2.

=MMULT(A2:B3, D2:E3)

Upon successful entry (using Ctrl+Shift+Enter for older versions or simply Enter for dynamic arrays), Excel calculates the product. The key to understanding matrix multiplication lies in the dot product calculation: each element in the resulting matrix is derived from multiplying the corresponding row from the first matrix by the corresponding column from the second matrix.

Reviewing the Mathematical Calculation for (2×2)

A detailed examination of the process reveals exactly how the MMULT function derives the resulting values. For example, the element in the first row and first column of the product matrix (G2) is calculated by taking the dot product of the first row of Matrix A and the first column of Matrix B. This involves summing the products of corresponding elements: (A2 * D2) + (B2 * D3).

Similarly, the element in cell H3 (second row, second column of the result) is calculated by multiplying the second row of Matrix A by the second column of Matrix B: (A3 * E2) + (B3 * E3). This structured approach ensures every element in the output matrix is mathematically correct according to the rules of linear algebra.

Here are the actual formulas that Excel utilized internally to calculate this matrix multiplication:

This meticulous computation results in the following final (2×2) product matrix, displayed across the range G2:H3:

Example 2: (2×2) by (2×3) Matrix Multiplication

This example moves beyond square matrices and demonstrates how to multiply a (2×2) matrix by a (2×3) matrix. This scenario confirms the compatibility rule: Matrix A has 2 columns, and Matrix B has 2 rows. Therefore, the operation is valid. Crucially, since Matrix A has 2 rows and Matrix B has 3 columns, the resulting product matrix will have dimensions (2×3).

The following screenshot illustrates the setup where the first matrix occupies A2:B3 and the second, larger matrix occupies D2:F3:

Note that the output range must accommodate the resulting (2×3) matrix, requiring six cells (e.g., H2:J3). We entered the following formula in cell H2 to initiate the multiplication process using the MMULT function:

=MMULT(A2:B3, D2:F3)

The ability of MMULT to handle different dimensions, provided the inner dimensions match, makes it incredibly versatile for various mathematical modeling tasks. Unlike standard element-wise multiplication, this function correctly performs the row-by-column summation inherent in matrix multiplication.

Analyzing the Resulting (2×3) Product Matrix

The calculation expands here because the second matrix has an additional column, demanding three separate column calculations for each row of the first matrix. For instance, the element in the second row, third column (J3) is calculated by multiplying the second row of Matrix A by the third column of Matrix B. This detailed process is encapsulated entirely by the MMULT function, simplifying complex calculations for the user.

Here are the actual formulas that Excel used to perform this extended matrix multiplication across the required output range:

This rigorous approach results in the following 2×3 product matrix, demonstrating how the output automatically adapts to the outer dimensions of the inputs:

The structure of the output is crucial: two rows inherited from the first matrix and three columns inherited from the second matrix.

Example 3: (3×3) by (3×2) Matrix Multiplication

Our final example involves larger matrices, multiplying a (3×3) matrix by a (3×2) matrix. Again, we verify dimensional compatibility: Matrix A (3×3) has 3 columns, and Matrix B (3×2) has 3 rows. The condition is met. The resulting product matrix will therefore be a (3×2) matrix, inheriting 3 rows from the first matrix and 2 columns from the second.

The following screenshot shows the arrangement where the (3×3) matrix spans A2:C4 and the (3×2) matrix spans E2:F4:

Since the result will be a (3×2) matrix, the output range must cover six cells (e.g., H2:I4). The formula entered in cell H2 to perform this large-scale matrix operation is:

=MMULT(A2:C4, E2:F4)

This showcases the scalability of the MMULT function. Regardless of the size of the input matrices, provided the dimension rules are satisfied, Excel computes the result accurately, which is invaluable for complex quantitative models.

Interpreting the Final (3×2) Output

The complexity of the calculation increases significantly with larger matrices, requiring the summation of three products for each element in the output matrix. For example, the element in H2 (first row, first column) involves the calculation: (A2 * E2) + (B2 * E3) + (C2 * E4). The MMULT function abstracts this complex arithmetic entirely, yielding the accurate final result.

Here are the actual formulas that Excel used to execute this detailed matrix multiplication:

This results in the following final (3×2) product matrix, confirming that the output dimensions are correctly determined by the rows of the first matrix and the columns of the second matrix:

Important Considerations for Using MMULT

When utilizing the MMULT function, several critical factors must be kept in mind to ensure successful computation and avoid common errors. Firstly, the input ranges (array1 and array2) must exclusively contain numerical data. If any cell within the defined range contains text, blank cells, or logical values (TRUE/FALSE), the function will return the #VALUE! error.

Secondly, the dimensional compatibility check is non-negotiable. If the columns of array1 do not match the rows of array2, the function will immediately return the #VALUE! error, confirming that the matrix algebra prerequisites have not been met. Users must visually or programmatically confirm that the inner dimensions align before executing the calculation.

Finally, while modern Excel versions handle array formula entry automatically (spilling the result), users of older versions must remember the legacy requirement: the output range must be pre-selected, and the formula must be committed using the Ctrl + Shift + Enter (CSE) key combination. Failure to use CSE will only return the first element of the result matrix rather than the complete matrix product.

Note: You can find the complete documentation for the MMULT function in Excel .