Matrix multiplication is a mathematical operation where you multiply two matrices to produce a new matrix. In Excel, you can easily perform matrix multiplication using built-in functions instead of doing each calculation manually.
Matrix multiplication is especially useful in data analysis, linear algebra, finance modeling, and machine learning tasks. For example, you might use it to calculate total sales from product-unit matrices or perform complex calculations for forecasting and optimization models.
In this article, you’ll learn how to do matrix multiplication in Excel step by step using different methods.
Here’s how to do matrix multiplication in Excel using the MMULT function:
➤ Open your dataset in Excel.
➤ Select the range where you want the result matrix to appear.
➤ Enter the following formula:
=MMULT(B3:C5, F3:G4)
➤ Press Enter or Ctrl + Shift + Enter in older versions.
➤ Excel will return the resulting matrix.
Using the MMULT Function for Matrix Multiplication in Excel
In the following dataset, we have two matrices:
Matrix A (3×2): Represents sales units of two products Product A and Product B across three regions such as North, South, and East.
Matrix B (2×2): Represents unit Prices and Costs of those products.
Our goal is to calculate the total revenue and total cost per region by multiplying Matrix A by Matrix B.
The MMULT function is the most direct and commonly used method to multiply two matrices in Excel. It takes two arrays as input and returns their matrix product.
Here’s how to do it step by step:
➤ Open your dataset in Excel.
➤ Before entering the formula, select the cell where you want the result to appear. Since Matrix A is 3×2 and Matrix B is 2×2, the result will be a 3×2 matrix. So, select the cell B9.
➤ Enter the following formula:
=MMULT(B3:C5, F3:G4)
➤ Press Ctrl + Shift + Enter if you’re using Excel 2019 or earlier. In Excel 365 or 2021, simply press Enter because the result will spill automatically into the necessary cells.
➤ Excel will now calculate the matrix multiplication and display the resulting values in the selected range.
Using SUMPRODUCT and TRANSPOSE Functions as an Alternative
If you prefer not to use the MMULT function or want to understand how matrix multiplication works behind the scenes, you can achieve the same result with a combination of the SUMPRODUCT and TRANSPOSE functions.
Here’s how to do it:
Step 1: Calculate Total Revenue
➤ Click on the first cell where you want the result to appear. For example, select cell B9.
➤ Enter the following formula:
=SUMPRODUCT(B3:C3, TRANSPOSE(F3:F4))
➤ Press Enter. Excel will return the first Total Revenue of the resulting matrix.
➤ To calculate the remaining cells, copy the formula across and down for the rest of the result range. You’ll need to adjust the row references of Matrix A (B4:C4, B5:C5, etc.) for each row. For example:
=SUMPRODUCT(B4:C4, TRANSPOSE(F3:F4))
=SUMPRODUCT(B5:C5, TRANSPOSE(F3:F4))
Step 2: Calculate Total Cost
➤ Click on cell C9 to display Total Cost for the first row (B3:C3).
➤ Enter the following formula:
=SUMPRODUCT(B3:C3, TRANSPOSE(G3:G4))
➤ Press Enter. Excel will return the first Total Cost of the resulting matrix.
➤ To calculate the remaining cells, copy the formula across and down for the rest of the result range. You’ll need to adjust the row references of Matrix A (B4:C4, B5:C5, etc.) for each row. For example:
=SUMPRODUCT(B4:C4, TRANSPOSE(G3:G4))
=SUMPRODUCT(B5:C5, TRANSPOSE(G3:G4))
Multiplying a Column by a Row Array to Create a Matrix
Another way to perform matrix multiplication in Excel is by multiplying a single column by a single row to generate a full matrix. This technique is especially useful when you want to calculate the contribution of one set of values across multiple categories.
In this dataset, we have a column representing total units sold for three regions and a row representing unit prices of three products: Product A, B, and C. In this method, we’re multiplying a 3×1 column by a 1×3 row, the result will be a 3×3 matrix.
Here’s how to do it:
➤ Open your dataset in Excel.
➤ Select a blank cell where the result matrix will appear. For example, click on cell B9.
➤ Enter the following formula:
=B3:B5 * E3:G3
➤ Press Ctrl + Shift + Enter if you’re using Excel 2019 or earlier. In Excel 365 or 2021, press Enter , and the results will spill automatically.
➤ Excel will multiply each value in the column by each value in the row and return a complete matrix.
Frequently Asked Questions
How do I multiply two matrices of different sizes in Excel?
You can only multiply matrices if the number of columns in the first matrix equals the number of rows in the second. Resize or restructure your data to meet this rule.
Can I use matrix multiplication to calculate weighted totals?
Yes. Matrix multiplication is commonly used to calculate weighted sums, portfolio returns, and other aggregate results based on weights and values.
Why am I getting a #VALUE! error with MMULT?
The most likely reason is a dimension mismatch. Ensure your matrices have compatible sizes: the number of columns in the first matrix must match the number of rows in the second.
Wrapping Up
Matrix multiplication in Excel is a powerful way to analyze data and perform advanced calculations. The MMULT function is the easiest and most commonly used method, but you can also use dynamic arrays, or the SUMPRODUCT and TRANSPOSE functions to achieve the same result.
You can even multiply a single column by a single row to create a full matrix, which is useful for quick calculations like revenue or cost breakdowns.
