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.
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.
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.
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.
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.
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.
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.