Excel Array Formulas
An Excel Array Formula performs multiple calculations on one or more sets of values 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
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
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
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 the keyboard combination Ctrl + Shift + Enter (see below for further details on inputting array formulas).
You can see that a formula has been entered as an array formula, as Excel inserts curly braces around the formula as seen in the formula bar of the above results spreadsheet.
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 enter your array formula into;
- Type the array formula into the first cell (or if already typed into the first cell, put this cell into edit mode by pressing F2 or clicking in the formula bar);
- Press Ctrl + Shift + Enter (i.e. press the Ctrl and Shift keys, and while keeping these 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 the 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:
- Make the required alterations in any single cell containing the array formula;
- Press Ctrl + Shift + Enter to update the whole array.
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 of the 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
Imagine 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.
One 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: 
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 for Excel to interpret it as 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 an in-depth guide with further examples on the Microsoft Office Website.