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.
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.
Type your formula in the formula bar, and press Ctrl+Shift+Enter.
Unfortunately, you can’t create three-dimensional arrays across multiple worksheets in