Array formulas in Excel are surrounded by curly braces “{ } “. These are added to a formula by pressing the CTRL+SHIFT+ENTER keys after typing the formula into a cell or cells.
Types of Array Formulas
There are two types of array formulas - those that are located in multiple cells in a worksheet (multi-cell array formula) and those located in a single cell (single cell array formula).
Single cell array formulas usually first carry out a multi-cell array calculation — such as multiplication — and then use a function such as or AVERAGE or SUM to combine the output of the array into a single result.
In the image above, the array formula first multiplies together those elements in the two ranges D1:D3 and E1:E3 that reside in the same row in the worksheet.
The results of these multiplication operations are then added together by the SUM function.
Another way of writing the above array formula would be:(D1 * E1) + (D2 * E2) + (D3 * E3)
At this point, leave the worksheet as is. The formula will be completed in the last step of the tutorial when the array formula is created.
Creating an array formula in Excel is done by pressing the CTRL+SHIFT+ENTER keys on the keyboard.
The effect of pressing these keys together is to surround the formula with curly braces: { } indicating that it is now an array formula.
Tutorial Steps
For help with these steps see the image above.
