Leading the way in Microsoft Office Development

What is an Array Formula ?

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

The main advantage of using an array formula is that your data can be manipulated as a whole block and passed to a function or formula as a single unit.

It allows you to apply a single formula to a range of cells, returning either a single value or an array of values (each value being in a separate cell).

Array formulas allow you to work on a range of cells without having to refer to all the individual cells in your formulas.

Excel automatically puts curly brackets {} around any array formulas. These brackets cannot be entered manually.

Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns.

Using Array Constants

Array constants are a list of values that can be used as arguments in your array formulas.

Arrays can be either 1-dimensional or 2-dimensional depending on the number of rows and columns.

A 1-dimensional array can exist in a single row or a single column.

One dimensional array {1, 2, 3, 4}

A 2-dimensional array can exist in a block of cells, made up of multiple rows and columns.

Two dimensional array {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}

Notice that vertical elements are separated by a semi-colon (;) as opposed to a comma.

An array constant can contain numbers, text, logical values as well as error values. Any text must be enclosed in double quotation marks (i.e. "some text").

An array constant cannot contain any numbers with commas, dollars, parentheses, percent signs, worksheet functions or any other arrays.

Arrays do not actually need to be stored in cells and can alternatively be stored in memory during a calculation.

When an array is stored in memory during a calculation the formula is executed slightly faster.

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.

Using array formulas will help to prevent the formulas being changed accidentally. It is not possible to change the contents of a single cell in an array formula. You must change all the cells that contain the formula.

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

Examples of array formula

There are several ways to obtain the same result using formulas. In most cases you can obtain the same result without using array formulas.

The following examples demonstrate two possible ways to use array formulas.

1) Adding up values [Using a multi cell array formula]

This example illustrates two methods that can be used to add two block of numbers.

The formulas in rows 8 and 9 use the SUM() worksheet function to add up the values.

The formulas in rows 11 & 12 use an array formula to add up the values and must be entered using (Ctrl + Shift + Enter). This array formula performs the necessary calculation and then returns a four-item array.

Excel inserts curly brackets {} around the formula to indicate that it is an array formula. These brackets cannot be entered manually.

The array formula "{=A1:B2+A4:B5}" returns an array containing four numbers.

The formula must therefore occupy four cells since you cannot display more than one number in a cell.

2) Getting subtotals [Using a single cell array formula]

This example illustrates two methods that can be used to obtain subtotals from a table of data.

The formulas in cells C12 & C13 use the plus operator to sum the individual cells.

Entering these manually would be quite time consuming if the list was any longer. Each cell must be added manually to the formula.

The formulas in cells C15 & C16 use an array formula to add up the subtotals and must be entered using (Ctrl + Shift + Enter), otherwise #VALUE! will be returned.

These array formulas use two 1-dimensional arrays, C3:C10 and D3:D10.

The IF() function compares the corresponding values in the first array C3:C10 and then returns the corresponding value from the second array D3:D10.

The SUM() function is then passed this new array (which exists only in memory) and returns the total value.

Things to Remember

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.