Array Formulas

Also known as CSE Formulas or (Ctrl + Shift + Enter) Formulas.
An array formula is a special type of formula that works specifically with arrays.
An array formula can produce a single result or multiple results where each result is displayed in a separate cell.
An array formula can occupy a single cell or a range of cells.
An array is basically just a collection of data of the same type that can be treated as a single entity. This is held in memory.
For those of you that are not familiar with the concept of an array, it is just a collection of values (in this case typically numbers).
Excel automatically puts curly brackets {} around any array formulas. These brackets cannot be entered manually.

Example 1 - The table below shows how you can use an array constant.

Exactly the same result can be obtained by placing the numbers into an array and passing the array (as a whole) to the worksheet function.
Array Constants can contain numbers, logical values (i.e. True or False) or text.
You can use different types of data in the same array constant.

Example 2 - The table below shows more array constants.

Difference with Array Formulas

Example 3 - The table below shows more array constants.

All the formulas in column F are equivalent and all return the total 110.
Formulas can work with arrays that are contained in cells, arrays that are constant as well as array formulas.

Advantages of using Array Formulas

Array formulas guarantee that all the formulas are the same and is a good way to ensure consistency among your formulas.
Using array formulas can speed up the time taken for formulas to be evaluated since the arrays can be stored in memory.
A workbook that contains array formulas will mean that the formulas are more efficient and will use less memory helping to reduce file sizes.
Array formulas help to protect your formulas as you cannot change, clear or move any individual cells that are part of an array block. It is not possible to change the contents of a single cell in an array formula. The only way to change the individual cells to treat the whole block as a single unit and change the whole block at the same time.
You can only delete an array formula by selecting the whole array block first. You cannot delete part of it.
Using an array formula will almost always be quicker than defining a custom function using VBA.
A worksheet that contains array formulas will be harder to understand, meaning that novices are less likely to change the formulas, especially if they do not understand how they work.
Array formulas do make it possible to perform some calculations that would otherwise be impossible using traditional formulas.

Disadvantages of using Array Formulas

You should try to avoid using a lot of large array formulas in a single workbook. This will reduce your workbook performance as well as making your formulas a nightmare to understand.
To many array formulas will slow down recalculation, saving, opening and closing.
Array formulas use less memory and they can eliminate the need for intermediate formulas, although they can slow the recalculation time of your spreadsheet and make it more complicated.
You cannot use column references (such as "A:A" or "D:D") in your array formulas.
You must always enter them using (Ctrl + Shift + Enter).

Important

Using a lot of array formulas in one workbook will increase the time taken to re-calculate.
The majority of the calculations that can be performed using array formulas can also be performed using regular formulas. Although there are a few exceptions.
If you accidentally enter an array formula by not pressing (Ctrl + Shift + Enter) then the formula will either return an incorrect value or #VALUE! will be returned.
To quickly select the cells which contain an array block you can use the shortcut key (Ctrl + / ).
A group of cells that is occupied by the same array formula is often called an Array Block or Array Range.
You cannot change any of the individual cells within an array block.
You cannot use column references (such as "A:A" or "D:D") in your array formulas.
The number of rows must always be the same in all the cell ranges. This is to ensure that all the temporary arrays used are the same length.

Entering

Array formulas are extremely useful and can be used directly in your formulas as well as being used just to display.
Highlight the range of cells (or an individual cell).
Enter the formula into the Formula Bar located above the column headings (as opposed to typing the formula directly into one of the cells).
Press (Ctrl + Shift + Enter) to insert the formula as an array formula. The curly brackets {} will be entered automatically for you. They cannot be entered manually.
If you enter an array into a range of cells that contains more cells than actual array elements then #N/A in entered in the other cells.

Using Single Row arrays

Highlight the cells B2:C2.
Enter the formula "={1,2}" into the formula bar.
Press (Ctrl + Shift + Enter).

The two values will appear to be separate although they are closely linked.

This array formula is automatically surrounded by curly brackets
Also notice that you cannot overwrite either of these values.
The only way to make changes to these cells is to select the whole range "B2:C2".

Using Multiple Row arrays

Highlight the cells B2:C3.
Enter the formula "={1,2;3,4}" into the formula bar (making sure you enter a semi-colon (;) between the 2 and the 3).
Press (Ctrl + Shift + Enter).

The four values will appear in four separate cells.

The only way to make any changes to these cells is to C217select the whole range "B2:C3".

Using Array formula with Worksheet Functions

You can combine the use of array formulas with the use of worksheet functions.
Highlight the cells B2:C3.
Enter the formula "=SQRT({1,4;16,36})".
Press (Ctrl + Shift + Enter).

The array formula is surrounded by curly brackets automatically.

Selecting and Editing Array Formulas

When an array formula occupies multiple cells you cannot edit the contents of an individual cell.
You must select all the cells that contain the formula before it can be edited in the formula bar.
You cannot edit, clear, move, insert or delete individual cells within an array block. The array block must be treated as a single unit.
You can select the cells that contain an array formula by highlighting any cell and selecting (Edit > GoTo > Special) and select the "Current array" option button.
Alternatively you can press (Ctrl + / ) to select the entire array block.
To confirm the new array formula you must press (Ctrl + Shift + Enter).

Important

Array formulas are surrounded with curly brackets ( {} ). These brackets cannot be entered manually.
An array formula can exist in a single cell or in multiple cells.
If you enter an array formula into a range of cells you cannot move any of the individual cells - The group of cells must be moved as whole.
You can remove an array formula by selecting the whole array block and pressing Delete.
If you edit an array formula and just use Enter instead of (Ctrl + Shift + Enter) then the formula will either return an incorrect value or #VALUE! will be returned.