Microsoft Office Tutorials and References

In Depth Information

**Array formula rules**

Array formula rules

To enter an array formula, first select the cell or range that will contain the results. If the

formula produces multiple results, you must select a range the same size and shape as the

range or ranges on which you perform your calculations.

Follow these guidelines when entering and working with array formulas:

Press Ctrl+Shift+Enter to lock in an array formula. Excel then places a set of curly

braces around the formula in the formula bar to indicate that it’s an array formula.

Don’t type the braces; if you do, Excel interprets your entry as text.

●

You can’t edit, clear, or move individual cells in an array range, and you can’t insert or

delete cells. You must treat the cells in the array range as a single unit and edit them

all at once.

●

To edit an array, select the entire array, click the formula bar, and edit the formula.

Then press Ctrl+Shift+Enter to lock in the formula.

●

To clear an array, select the entire array and press Delete.

●

To select an entire array, click any cell in the array and press Ctrl+/.

●

To move an array range, you must select the entire array and either cut and paste the

selection or drag the selection to a new location.

●

You can’t cut, clear, or edit part of an array, but you can assign different formats to

individual cells in the array. You can also copy cells from an array range and paste

them in another area of your worksheet.

●

Two-dimensional arrays

In the preceding example, the array formula resulted in a vertical, one-dimensional array.

You also can create arrays that include two or more columns and rows, otherwise known as

two-dimensional arrays
. Figure 12-33 shows an example.

To enter a two-dimensional array, do the following:

Select a range to contain your array.

1.

Type your formula in the formula bar, and press Ctrl+Shift+Enter.

2.

Note

Unfortunately, you can’t create three-dimensional arrays across multiple worksheets in

workbooks.