Excel Array Formulas

An Excel Array Formula performs multiple calculations on one or more sets of values (the 'array arguments') and returns one or more results.

This is best described by way of an example:

We require a transpose from cells B1:B3 into cells A5:C5
Excel Array Formula Example Part1

Suppose you are working in the above spreadsheet on the right, and you wish to use the Excel Transpose Function to copy the contents of cells B1:B3 into cells A5:C5.


Incorrect Method:

Simple formula returns #VALUE error
Excel Array Formula Example Part2

If you simply type the function

=TRANSPOSE( B1:B3 )

into the cells A5:C5 (as shown aboveon the right), you will get the Excel #VALUE! error message, because in this case, the cells are working independently and so the function doesn't make sense to each individual cell.


Correct Method:

Result - obtained by inputting as an array formula
Excel Array Formula Example Part2

In order to make sense of the Transpose Function, we need to get the cells A5:C5 to work together as an ARRAY. Therefore we need to enter the function as an Excel array formula.

The array formula is entered by pressing CTRL-SHIFT-Enter (see below for further details on inputting array formulas). This puts curly braces around the formula as seen in the formula bar of the above results spreadsheet on the right.


Inputting Excel Array Formulas

In order to be understood as an array formula, a formula must be input in the following way:

  • Highlight the range of cells that you want to input your array formula into
  • Type the array formula into the first cell (or if already typed into the first cell, make this cell editable by pressing F2 or clicking in the formula bar
  • Press CTRL-SHIFT-Enter (ie. press the CTRL and SHIFT keys, and while these are pressed down, press the Enter key

You will notice that Excel automatically places curly braces { } around array formulas. Note that these must be inserted by Excel, as a result of the steps outlined above.

If you attempt to type the curly braces in yourself, Excel will not interpret your formula as an array formula.


Editing Excel Array Formulas

Excel will not allow you to edit just one part of a range of cells that contain an array formula, as the cells all work together as a group.

Therefore, in order to edit an Excel array formula, you need to:


Deleting Excel Array Formulas

Excel will also not allow you to delete part of an Excel Array Formula. You must delete the formula from all cells that it occupies.

Therefore, if you want to remove an array formula from a range of cells you need to highlight the whole cell range, and then press the delete key.


Excel Array Formula Example 2

Suppose you are working on the example spreadsheet below, and you want to multiply each of the values in cells A1:A5 with the corresponding values in cells B1:B5, and then sum all these values.

The simplest way to perform this task is with the use of the array formula,

=SUM( A1:A5 * B1:B5 )

This is shown in the formula bar of the result spreadsheet below.

  Original Spreadsheet:

Excel Array Formula Example

Require Calculation of A1*B1 + ... + A5*B5

  Result:

Excel Array Formula Result

Calculation Done Using an Array Formula

Note that, even though the array formula in the above spreadsheet is only entered into one cell, you still need to enter the formula using CTRL-SHIFT-Enter to ensure that Excel understands it to be an array formula.


Further Excel Array Formula Examples

For a further examples of an Excel Array Formula, see the examples in the Excel Frequency Function or the Excel Trend Function page.

If you want to learn more about Excel Array Formulas, there is a more in-depth explanation, with further examples, on the Microsoft Office Website.