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 :



Suppose I am working in the spreadsheet on the right, and I wish to use the Excel Transpose Function to copy the contents of cells B1:B3 into cells A5:C5.
We require a transpose from cells B1:B3 into cells A5:C5
Excel Array Formula Example Part1

Incorrect Method:

If I simply type

=TRANSPOSE( B1:B3 )

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


Simple formula returns #VALUE error
Excel Array Formula Example Part2

Correct Method:

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.

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

Inputting Excel Array Formulas

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

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 :

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,

=SUM( A1:A5 * B1:B5 )

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

Excel Array Formula Example
Require Calculation of A1*B1 + ... + A5*B5
Excel Array Formula Result
Calculation Done Using 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 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.

Return to the ExcelFunctions.net Home Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013 ExcelFunctions.net