Leading the way in Microsoft Office Development

Step 1 - Entering array formula

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.

Step 2 - 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".

Step 3 - 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".

Step 4 - 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.

Step 5 - 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).

Step 6 - Things to Remember

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.