ExcelFunctions.net

Search Site:

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 an example :

We require a transpose from cells B1:B3 into cells A5:C5

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

Simple formula returns #VALUE error

If you simply type

=TRANSPOSE( B1:B3 )

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

Result - obtained by inputting as an array formula

In order to make sense of the Transpose Function, we need to get the cells A5:C5 to work together as an ARRAY - ie. 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 results spreadsheet.

In order to be understood as an array formula, you have to input your formula as follows :

- 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 note that, Excel automatically places curly braces { } around array formulas. However, these
**must** be inserted by Excel, as a result of the steps outlined above
- typing these in yourself will **not** result in your formula being processed by Excel as an array formula.

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 :

- Select one of the cells in the array formula
- Make your alterations in the selected cell
- Press CTRL-SHIFT-Enter to update the whole array

If you want to remove the array formula from the range of cells you need to highlight the whole range, and then press the delete key, to erase the contents of the whole array as once.

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 results spreadsheet below.

Original Spreadsheet: Require Calculation of A1*B1 + ... + A5*B5 |
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 the formula to be an array formula.

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

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.