Matrix in Excel (Table of Contents)
- Introduction to Matrix In Excel
- Calculation methods of Matrix in Excel
- The Inverse of Matrix in Excel
- The Determinant of Square Matrix in Excel
Introduction to Matrix In Excel
A Matrix is an array of elements. It mostly took the rectangular shape when formed. It’s been arranged in rows and column. It is used to show the placement of two elements along two axes. You can use a matrix to illustrate nine possible combinations of three elements. Most of the MS Excel functions you’re using to perform Matrix operations are array functions which provide multiple values at a time. To create Matrix in MS Excel just enter the data of the matrix as shown in the below screenshot. The above matrix is a {3X3} matrix and its elements are numerics 1 to 9.
Naming a Matrix
Now it’s important to give a unique name to every matrix you make.
So we can do the further calculations easily by providing just a name of that matrix.
To give a name to the matrix, select all the elements of the matrix as per the fig. 2 and give it a name shown as per the fig. 3, For this example, we have given this matrix a name “AA”.
Calculation Methods of Matrix in Excel
There are two methods for the calculation of Matrices
- Brute Force Method (Cell reference method)
- Built-in array method
A) Brute Force Method
Addition of Matrices:
- For example, we have made two matrices here named A & B. For addition with this method make the sum of both the 1st element respectively, then select the column and drag down the array till the third row and then select these 3 columns and drag it to left till the third column.
- Now you can see the addition of these cells shown in the new matrix.
Subtraction in Matrices:
- To subtract a matrix from a matrix, look into the image below for your reference and follow the steps. As you can see in the formula bar you need to subtract A8 from A3, for that the formula became =A3-A8 you’ll get -9 as a result because 1-10 = -9. As per the image you can see the black dot you have to drag 2 steps to right.
- As per image #2, you can see that you can do the subtraction of all the elements.
B) Built-in Array Method
Addition in Matrices:
- For example, we have made two matrices here named A & B. For the addition of these both matrices, we have to highlight 3X3 space in the spreadsheet as both the Matrices A and B we adding are of 3X3 elements.
- Now you need to select 3X3 space in a spreadsheet just enter the simple addition formula =A+B and then press Shift +Ctrl+Enter and you’ll have your addition of matrices (Note that the Braces will surround the formula).
Subtraction in Matrices:
- Similarly to the addition, we just need to change the formula for this calculation instead of = A+B we will enter =A-B for this calculation.
- After selecting 3X3 space in a spreadsheet just enter the simple addition formula =A-B and then press Shift +Ctrl+Enter and you’ll have your subtraction of matrices.
Multiplication in Matrices:
- Now this one is tricky, don’t you think that it will be the same as addition and subtraction. Same as all examples here also we need two matrices for multiplication, so let’s make two different Matrices and give names as Matrix G and Matrix J. Both of these matrices are of 3X3 elements.
- Now for the Multiplication of the Matrices, there is not a regular calculation as it was in addition and subtraction, for multiplication of the Matrices you need to follow the procedure. As we have given Names to our Matrices, now for the Multiplication of the Matrices we need to select space of 3X3 and apply the formula =MMULT(G, J), After applying the above formula just press Ctrl+Shift+Enter.
- You’ll find that the selected area of 3X3 is showing the Multiplication of the Matrix G and Matrix J.
Transpose of a Matrix:
- To learn to transpose Matrix we will take the Matrix of 2X3 elements. For example, let’s take a Matrix of 2X3 and give it a Name “AI”. The transpose of Matrix I will result in 3X2. So select the 3X2 space in your spreadsheet. Now write down the transpose formula =TRANSPOSE(I) instead of I we can also use the range of the matrix which is A3 C4. Now Press Ctrl+Shift+Enter you will find the transpose of Matrix I. The mathematical representation for the transpose of Matrix I is Matrix I
- Matrix I is of 3X2 of elements.
The Inverse of Matrix in Excel
Now to find Inverse of a Matrix follows the procedure as below:
- The mathematical representation for an Inverse matrix E denoted by E-1
- Make a Matrix E of 3X3 for example, the Inverse of this matrix will be Matrix E and it will also result in 3X3. Now write down the transpose formula =MINVERSE(E) instead of E we can also use the range of the matrix which is A10 C12.
- Now press Ctrl+Shift+Enter you will find the Inverse of Matrix E we can call it Matrix E-1
The Determinant of Square Matrix in Excel
- This is very useful when it comes to using excel for matrix equations, It’s been a very lengthy method to find the determinant of a Matrix in general, but in excel you can get it just by entering a formula for it.
- The formula to find the determinant of a Square Matrix in Excel is =MDETERM(Array) the space of Array has to be filled either by the name of the array or the range of the array of which determinant we want to find. As you all know the determinant of a Matrix is not result in a Matrix, it just needs a cell for the answer, that’s why we don’t need to select the matrix space before applying formula. Now suppose for this we make a Matrix F and to find the determinant of the Matrix F, the formula will be =MDETERM(F).
- You can see from the images that for our given Matrix F’s determinant is -1, so in a mathematical representation, you can write Matrix F = -1.
Recommended Articles
This is a guide to Matrix in Excel. Here we discuss the Calculation Method, Inverse and Determinant of Matrix along with examples and downloadable excel template. You may also look at these useful functions in excel –
4.8 (2,393 ratings)
View Course
