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

Inputting Excel Array Formulas

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.

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 :

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

Excel Array Formula Example 2

Suppose I am working on the example spreadsheet below, and I 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,

This is shown in the results spreadsheet on the right below.

Further Excel Array Formula Examples

For a further examples of an Excel Array Formula, see the examples in the following 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 (link opens in a new window).