How to Create a Matrix Spreadsheet

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.

The Merriam Webster dictionary defines a matrix as a "rectangular array of mathematical elements." Microsoft refers to these entities as "arrays." Whichever term you use, spreadsheets with matrices let you perform calculations that you can't perform with standard functions. For example, you can use them to find the three smallest or largest values in a range. Using array functions in Excel involves working with ranges of cells instead of single values. It also involves keystrokes and characters reserved specifically for arrays. A third aspect of creating matrix spreadsheets is the use of functions that have inputs or output as an array.

Advertisement

Matrix Addition

Video of the Day

Step 1

Open a new workbook and type a column of numbers on the current spreadsheet. Type header text in the cell above the numbers, such as "List1." Type another column of numbers immediately to the right of the first column, ensuring the second column has the same length as the first column. Type "List2" or similar header text for this column.

Advertisement

Video of the Day

Step 2

Select the first column and enter "List1" in Excel's Name box, which is next to its Formula bar. Select the second column and enter "List2" in Excel's Name box. This step assigns names to the columns, which makes it easier to refer to them when writing calculations.

Advertisement

Step 3

Select a range of blank cells that has the same number of rows as the numeric columns you entered. Press the "=" keyboard key and type "List1+List2," but do not press "Enter." Doing so will enter a calculation in only one cell.

Step 4

Press "Ctrl-Shift-Enter" to enter your summation calculation in each cell of the selected range. The cells display the sum of the numeric columns you entered. Observe that each cell contains the same formula, and is surrounded by curly braces. These characters indicate that the formula is part of an array or matrix.

Advertisement

Find Smallest and Largest Values in a Range

Step 1

Create a new spreadsheet and type a range of numbers that spans at least two columns and two rows. Select this range and type "myArray" in Excel's Name text box.

Advertisement

Step 2

Select a blank range of cells one column wide and three rows deep. Press the "=" key and type "small (". Small is Excel's function for finding a range's smallest values.

Advertisement

Step 3

Type "myArray", followed by a comma. Type "{1;2;3})", including the semicolons and closing parenthesis. Press "Ctrl-Shift-Enter" to create an output array containing the three smallest values of the "myArray" array.

Step 4

Repeat the procedure to create an array of the three smallest values, except type "large" instead of "small" to see the largest three values.

Advertisement

Advertisement

references