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 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:
=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.
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.
In order to be understood as an array formula, you have to input your formula as follows :
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.
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,
This is shown in the results spreadsheet below.
Require Calculation of A1*B1 + ... + A5*B5
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.
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.