Array Names and Matrix Functions in Microsoft Excel^®

This is a demonstration of a convenient feature of the Excel spreadsheet that is not well documented in the online help files.   Although this illustration is taken from Excel2002 for Windows XP Office, a similar procedure should work in earlier versions of Excel, back to 1997 at least.

•   Naming an Array
•   Determinant of a Square Matrix
•   Inverse of a Square Matrix
•   Matrix Multiplication
•   Formatting Inside a Cell

Naming an Array

Let us suppose that you wish to associate a block of cells with a square matrix A. In this illustration, the values are held in cells C2:E4, but in general any rectangular block of m rows by n columns may be associated with an   m×n matrix.

Click on the top left cell in your array, drag the cursor to the opposite corner (to highlight all cells in your array) and release the cursor. Then click on the down arrow beside the “Name Box” near the top left corner of the Excel window.   The reference to the top left cell in your array is highlighted.

Type the name that you have chosen for your matrix (in this example, A) and press the Enter key. This will replace the cell reference, but it is now a reference to the entire highlighted block of cells, not to any one of them. In this example, "A" is now an alias for the cell range C2:E4.

Determinant of a Square Matrix

Now let’s place the determinant of our matrix in cell C6. Click the cursor on the cell.   Then press the "=" key to start formula entry in that cell (or press the fx or = key next to the edit box). Click the down arrow on the function selection box that now appears.   If MDETERM is present in the drop-down list of functions, then click on it.   If not, click on the last option,   More Functions....

If you had to click on the option,   More Functions..., then this dialog box appears.   [Note that its layout has changed somewhat between Excel2000 and Excel2002.   The essential features are not that different.] You can select the category "All", but there will be far fewer functions in the list if you scroll down to and click on "Math & Trig".

Now scroll down the "Select a function" window until you find the function MDETERM, then click on it and click the OK button.

A new window opens to allow you to select a range of cells as the Array argument of the MDETERM function.

Just type the label that you chose for your matrix, (in this example, A).   This replaces the range of cells offered by Excel. Then click the OK button to close this window.

The edit box now shows the correct formula for the determinant of your matrix, so just press the Enter key. [An aside: If you don’t know how to obtain the mixture of formatting inside a single cell that you can see here, then click here.]

Inverse of a Square Matrix

Now let us suppose that we wish to place the inverse of matrix A in cells C8:E10. Begin, as before, by highlighting the range of cells and then clicking on the down arrow beside the Name Box. Below the highlighted reference to the top left cell C8 in your array range is a list of all other array names that you have created in this workbook.   In this case, there is only one previous named array:   A.

Just replace the reference to cell C8 by typing a name for your inverse matrix (in this example, Ainv).   Then press the Enter key. The label Ainv is now an alias for a cell range (C8:E10 in this example).

Keep all of cells C8:E10 selected and press the = key to begin formula entry. Again use the drop down box of Excel functions.   If MINVERSE is not in the list, then click on More Functions....

Select the category "Math & Trig", then scroll down the "Select a function" window until you find the function MINVERSE, then click on it and click the OK button.

A new window opens to allow you to select a range of cells as the Array argument of the MINVERSE function.
Just type the label that you chose for your matrix, (in this example, A).
This replaces the range of cells offered by Excel.

IMPORTANT:

Do not click the OK button!
(Do not press Enter either).

There is a special way to enter an array formula into all of the cells of another array:
Press the three-key combination   Shift+Ctrl+Enter.

Upon releasing the   Shift+Ctrl+Enter   combination, you should see this display: Note the braces   { }   around the formula   =MINVERSE(A)   in the edit box.   You cannot enter this formula manually for any cell - copy and paste won’t work either.   You must use the   Shift+Ctrl+Enter   method while all cells in the desired array are highlighted (selected).

Matrix Multiplication

An example is shown here of the use of a matrix inverse and matrix multiplication to solve a matrix equation   Ax = b .

While the cells for the solution matrix are selected: Press the = key; Click the down arrow on the function selection box; Click on the last option,   More Functions.... In the "Insert Function" window shown here: select the category "Math & Trig"; scroll down the "Select a function" window until you find the function MMULT, click the OK button (or press Enter).

Instead of the tedious business of entering ranges of cells as the arguments of this function, you need only type the names that you have given to the arrays.   (In this example, the cell range G2:G4 has been given the array name b.) IMPORTANT: Do not click the OK button! (Do not press Enter either). There is a special way to enter an array formula into all of the cells of another array: Press the three-key combination   Shift+Ctrl+Enter.

If done correctly, your spreadsheet should now look like this:

Formatting Inside a Cell

Starting with

Inside the edit box, click the cursor at the first character to be converted to boldface and drag to the last of the consecutive characters to be made bold.

Then either click on the bold button B in the edit toolbar, or press the shortcut keys Ctrl+B.

Repeat for any other characters to be made bold, then press Enter.

To raise text characters inside a cell to superscript:
Select the characters in the edit box;
Click on Format in the top menu bar;
Click on Cells....

Any changes that you make now to formatting, using this "Format Cells" dialog box, will affect only the selected text, not the entire cell. For superscript, just check the "Superscript" option box in the "Effects" pane of this window, then click the OK button (or press Enter).

You may have noticed that the minus sign in the superscript is now too small in most fonts.   The symbol font will correct that problem. Select just the minus sign in the edit box, then open the font drop down box from the edit toolbar. Click on the new font.

You now have a mixture of fonts inside a single cell!

Created 2002 09 26 and last modified 2002 10 09 by
Dr. G.H. George