Working with Array Formulas | Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

This section deals with the mechanics of selecting cells that contain arrays as well as entering and editing array formulas. These procedures differ a bit from working with ordinary ranges and formulas.

Entering an Array Formula

When you enter an array formula into a cell or range, you must follow a special procedure so Excel knows that you want an array formula rather than a normal formula. You enter a normal formula into a cell by pressing Enter. You enter an array formula into one or more cells by pressing Ctrl+Shift+Enter.

You can easily identify an array formula because the formula is enclosed in curly brackets in the formula bar. The following formula, for example, is an array formula:

 {=SUM(LEN(A1:A5))}

Don't enter the curly brackets when you create an array formula; Excel inserts them for you after you press Ctrl+Shift+Enter. If the result of an array formula consists of more than one value, you must select all of the cells in the results range before you enter the formula. If you fail to do this, only the first element of the result is returned.

Selecting an Array Formula Range

You can select the cells that contain a multicell array formula manually by using the normal cell selection procedures. Alternatively, you can use either of the following methods:

Activate any cell in the array formula range. Choose Home Editing Find & Select Go To Special, and then choose the Current Array option. When you click OK to close the dialog box, Excel selects the array.
Activate any cell in the array formula range and press Ctrl+/ to select the entire array.

Editing an Array Formula

If an array formula occupies multiple cells, you must edit the entire range as though it were a single cell. The key point to remember is that you can't change just one element of an array formula. If you attempt to do so, Excel displays the message shown in Figure 14-6. Press Esc to exit Edit mode; then select the entire range and try again.

image from book
Figure 14-6: Excel's warning message reminds you that you can't edit just one cell of a multicell array formula.

The following rules apply to multicell array formulas. If you try to do any of these things, Excel lets you know about it:

You can't change the contents of any individual cell that make up an array formula.
You can't move cells that make up part of an array formula (although you can move an entire array formula).
You can't delete cells that form part of an array formula (although you can delete an entire array).
You can't insert new cells into an array range. This rule includes inserting rows or columns that would add new cells to an array range.

To edit an array formula, select all the cells in the array range and activate the formula bar as usual (click it or press F2). Excel removes the brackets from the formula while you edit it. Edit the formula and then press Ctrl+Shift+Enter to enter the changes. Excel adds the curly brackets, and all the cells in the array now reflect your editing changes.

Caution

If you accidentally press Ctrl+Enter (instead of Ctrl+Shift+Enter) after editing an array formula, the formula will be entered into each selected cell, but it will no longer be an array formula.

Array Formulas: The Downside

If you've read straight through to this point in the chapter, you probably understand some of the advantages of using array formulas. The main advantage, of course, is that an array formula enables you to perform otherwise impossible calculations. As you gain more experience with arrays, you undoubtedly will discover some disadvantages.

Array formulas are one of the least understood features of Excel. Consequently, if you plan to share a workbook with someone who may need to make modifications, you should probably avoid using array formulas. Encountering an array formula when you don't know what it is can be very confusing.

You might also discover that you can easily forget to enter an array formula by pressing Ctrl+Shift+Enter. If you edit an existing array, you still must use these keys to complete the edits. Except for logical errors, this is probably the most common problem that users have with array formulas. If you press Enter by mistake after editing an array formula, just press F2 to get back into Edit mode and then press Ctrl+Shift+Enter.

Another potential problem with array formulas is that they can slow your worksheet's recalculations, especially if you use very large arrays. On a faster system, this may not be a problem. But, conversely, using an array formula is almost always faster than using a custom VBA function. (Part VI of this book covers custom VBA functions.)

Although you can't change any individual cell that makes up a multicell array formula, you can apply formatting to the entire array or to only parts of it.

Expanding or Contracting a Multicell Array Formula

Often, you may need to expand a multicell array formula (to include more cells) or contract it (to include fewer cells). Doing so requires a few steps:

Select the entire range that contains the array formula. You can use Ctrl+/ to automatically select the cells in an array that includes the active cell.
Press F2 to enter Edit mode.
Press Ctrl+Enter. This step enters an identical (non-array) formula into each selected cell.
Change your range selection to include additional or fewer cells.
Press F2 to reenter Edit mode.
Press Ctrl+Shift+Enter.