A matrix in Excel does not have anything to do with Neo and Morphius, we’re just going to break that to you now. Instead, a matrix is an array of elements in a grouping of equal quantity rows and columns (e.g. a 3×3 cell data group). Matrices can be used to conduct complex calculations, and are super easy to create.
Let’s take a look at how to create and work with matrices in Excel. For all other information, see our FAQ section at the end of this article.
Table of Contents
How to create a matrix in Excel
- Open Microsoft Excel on your device and either open an existing project or start a new one.
- Input data into a rectangular group of cells, equal in column and row length (i.e. 3×3 or 1×1, etc.). Alternatively, highlight a pre-existing group of cells with data in them which conforms to this 1:1 ratio requirement
- This is your matrix, simple as that. From here, there are a few things you can do:
- Name your matrix by highlighting it (drag and click from the top-left cell to the bottom-right), and then inputting a name into the white box to the left of the formula options.
- Make your matrix clear and distinct by formatting it. Highlight it as shown above, then right-click inside the selection and choose ‘Format Cells’. Click the tab ‘Border’ and choose whichever kind of outline you like. Alternatively, you could achieve a similar effect by highlighting the cells with a colour of your choosing.
- Name your matrix by highlighting it (drag and click from the top-left cell to the bottom-right), and then inputting a name into the white box to the left of the formula options.
How to calculate with matrices in Excel (Brute Force method)
- Open the Microsoft Excel app and spreadsheet with your various matrices in it
- Now, to calculate (for example) the sum of two matrices using the Brute Force method, you want to select an empty cell anywhere on your spreadsheet, and input the sum formula =[MATRICE A CELL 1]+[MATRICE B CELL 1] (this may look, for example, like =A1+A6).
- Press Enter and your highlighted cell will now show the sum of the data entered into the first upper-left cells of your two matrices. To then apply this formula across both matrices, creating a third matrix containing the calculation, click and drag the small green square on the bottom-right corner of this recently calculated cell, and drag it to cover the same area as your other matrices (i.e. if calculating the sum of two 3×3 matrices, click and drag this box to cover also 3×3 cells)
- Let go of the click and drag and you’ll find the sum applied to the first cell of this third matrix now duplicated throughout all cells.
- You have now successfully calculated the sum of the data in two matrices, and contained the findings within a third, using the Brute Force method.
How to calculate with matrices in Excel (Built-In Array method)
- Open the Microsoft Excel app and spreadsheet with your various matrices in it
- Now, to calculate (for example) the subtraction of one matrix from another using the Built-In ArrayForce method, you want to click and drag to select an area equal to your other matrices (i.e. if your other two matrices are both 3×3, click and drag this box to also cover 3×3 cells).
- Next, type in your formula: to subtract Matrix A from Matrix B, for example (make sure you’ve named both matrices involved in the calculation), type =A-B
- Now, at this point do not press Enter. Doing so will give you a #VALUE! error. Instead, hit CTRL+SHIFT+ENTER. Your new matrix will populate with the findings from your calculation of subtraction.
- You have successfully calculated the subtraction of the data in two matrices, and contained the findings within a third, using the Built-In Array method.
And there you go. Not particularly easy to get your head around some of this terminology, but once you’ve spent a short while practicing we guarantee you’ll be a master matrix-er in no time. Happy Excelling! For any other information or queries, please see our FAQs below.
FAQs
What is a matrix in Microsoft Excel?
In Microsoft Excel, a matrix is actually called an ‘array’. Since arrays are arrays of elements in rows and columns (of equal quantity), used to show the placement of concepts along axes, a Microsoft Excel spreadsheet is, technically speaking, a matrix, or array, in and of itself.
However, when referring to matrices or arrays within Microsoft Excel, what we mean is any group of cells with data in them whose column and row quantity is equal (e.g. 3×3 cells). Any such grouping is called an array (or matrix), and can be formatted with color and/or a border for your own sake, in order to better differentiate matrices from each other.
You can name the matrices you create to make them easier to conduct calculations with later. To learn how to create and calculate with matrices, simply follow our step-by-step guides above.
Why would I want to create a matrix in Microsoft Excel?
Matrices, or arrays, are created often in Excel, given that they refer simply to any grouping of cells equal in column and row quantity (2×2, 1×1, 4×4, etc.). However, purposefully creating an array is most useful for then conducting calculations pertaining to that (and other) array(s).
Since you have all of the data you need to refer to contained within your matrices, you can quickly calculate large sums with that data simply by referencing the matrices themselves, rather than each individual cell. There are, however, two different means of calculating matrices, each with its own benefits.
What are the two main means of calculating using Microsoft Excel matrices?
You can calculate using the Brute Force method (inputting cell formulae manually and stretching it out to duplicate it across the area of a new matrix, creating, for example, a third matrix containing the summation of two other matrices).
Alternatively, you can use the Built-In Arrays method, whereby you create a new array with a calculation directly referencing (for example) two other arrays. The effect is the same as the Brute Force method, but arguably quicker and more concrete.
Which method of matrix calculation is better?
The answer to this really depends on your preferred method of working, as well as your intended outcome. If you are happy to work more manually, and don’t require matrices themselves to be included in your calculations, but only require the data within them to be calculated, then the Brute Force method is preferable.
However, if you require that your two or more matrices be directly referenced as a whole in your matrix calculations, then you shall have to use the Built-In Arrays method. Personally, we find that the built-in arrays method is neater, and guarantees with slightly greater surety the connection between various matrices.
