How to Do Matrix Multiplication in Excel (5 Examples)

A matrix is an important tool, used every day in statistics and scientific studies. Excel spreadsheets themselves are very large matrices containing 1,048,576 rows and 16,384 columns. It isn’t surprising that Excel provides some useful tools for matrix operations. In this article, we will focus on matrix multiplication in Excel with different examples of different scenarios.


Download Practice Workbook

You can download the workbook containing all the examples used in this article from the box below.


How to Do Matrix Multiplication?

First, let us focus on how matrix multiplication actually works. If there are two matrices with dimensions i x j and j x k, each element of the first row will be multiplied by elements of their respective entry numbers from the first column of the second matrix. Then all the results added will indicate the value of the element of row one and column one of the result matrix, taking row number from the first matrix and column number from the second. This will go on for i x k times and result in an i x k matrix.

Let’s take an example where we are adding two matrices A and B.

How to Do Matrix Multiplication in Excel

Every entry from the first row of matrix A will multiply with respective entries from the first column of matrix B. Then the result will give us the 1×1 value of the multiplied matrix, let’s say C. In this example it will be 1*4+2*6+3*8=40.

The same process will repeat for 1st row from A and 2nd column from B, 2nd row from A and 1st column for B, 2nd row from A and 2nd column from B.

Finally, the result will look something like this.

This is the multiplied matrix of A and B.


5 Suitable Instances to Do Matrix Multiplication in Excel

Excel has a built-in MMULT function for Matrix Multiplication. This function takes two arrays as arguments. We can use matrices as argument arrays in this function to get the desired result.


1. Matrix Multiplication of Two Arrays

Let’s take two individual matrices A and B. In Excel, we will treat them as arrays for matrix multiplication.

Steps:

  • First, select the cells you want to put your matrix in.

  • Then write in the following formula.

=MMULT(B5:D7,B10:D12)

How to Do Matrix Multiplication in Excel

  • Now, on your keyboard, press Ctr+Shift+Enter. You will have the result of the AxB matrix.

How to Do Matrix Multiplication in Excel

You can do the same for the BxA matrix by entering matrix B as the first and matrix A as the second argument of the MMULT function.

How to Do Matrix Multiplication in Excel

Read More: How to Multiply 3 Matrices in Excel (2 Easy Methods)


2. Multiply One Column with One Row Array

Let’s take the following dataset, with matrices containing only one column and one row.

The multiplied matrix AxB will be the result of the multiplication of one-column and one-row matrices.

Steps:

  • First, select the range of cells for the multiplied matrix.

How to Do Matrix Multiplication in Excel

  • Then write down the following formula.

=MMULT(B5:B7,B10:D10)

How to Do Matrix Multiplication in Excel

  • Finally, press Ctrl+Shift+Enter on your keyboard. You will have the resultant matrix.

How to Do Matrix Multiplication in Excel

Read More: How to Multiply Multiple Cells in Excel (4 Methods)


Similar Readings


3. One Row and One Column Array Multiplication in Excel

For the same dataset used in the previous method, matrix multiplication of BxA will indicate the multiplication of one row and one column matrices.

Steps:

  • First, select the cell. This multiplication will only yield one value, so select one cell here.

  • Then type in the following formula.

=MMULT(B10:D10,B5:B7)

How to Do Matrix Multiplication in Excel

  • Now, press Ctrl+Shift+Enter on your keyboard. You will have your desired result.

How to Do Matrix Multiplication in Excel

Read More: Multiplication Formula in Excel (6 Quick Approaches)


4. Calculate Square of a Matrix from Matrix Multiplication

Let’s go back to the matrices on the examples used in the first example. We will use matrix multiplication here to determine the squares of matrices A and B.

Steps:

  • Select the range of cells for your square matrix.

  • Now write down the following formula.

=MMULT(B5:D7,B5:D7)

How to Do Matrix Multiplication in Excel

  • Now, press Ctrl+Shift+Enter on your keyboard. You will have the square of the matrix A.

How to Do Matrix Multiplication in Excel

You can replace the range of matrix A with the range of matrix B (B10:D12) and get the square of matrix B too.

How to Do Matrix Multiplication in Excel

Read More: What is the Formula for Multiplication in Excel for Multiple Cells? (3 Ways)


Similar Readings


5. Multiplication of a Matrix and a Scalar

When a matrix is multiplied by only a number, all the elements of the matrix are multiplied by that number. This can also be achieved in Excel.

For the demonstration, I am using the matrix A here and multiplying it by 7.

Steps:

  • Select the range of cells for the multiplied matrix.

  • Then type the following formula in the box.

=B5:D7*G7

How to Do Matrix Multiplication in Excel

  • Press Ctrl+Shift+Enter on your keyboard.

How to Do Matrix Multiplication in Excel

Read More: How to Multiply in Excel: Columns, Cells, Rows, & Numbers


Errors While Doing Matrix Multiplication in Excel

There are several errors you can run into while performing matrix multiplication in Excel.

Of them, a #VALUE! error can occur if the number of columns in the first array and the number of rows in the second array doesn’t match.

How to Do Matrix Multiplication in Excel

You will have the same error if there is at least one non-numeric value in a cell within the array.

If you select more values than your supposed multiplied matrix looks like, you will have #N/A error, though only in the extra cells you have selected.

How to Do Matrix Multiplication in Excel

Read More: How to Divide and Multiply in One Excel Formula (4 Ways)


Limitation of Matrix Multiplication in Excel

If you are using Excel 2003 or an older version, there is a limit for matrix multiplication of 71×71 dimensions. But for the later versions, you can carry on the operation for as long as the spreadsheet allows, limited only by the RAM of your system.


Conclusion

These were the different situations you can do matrix multiplication in Excel. Hope you have found this article helpful and informative. If you have any questions or suggestions for us, let us know below.

For more guides like these, visit Exceldemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo