About Array Formulas
Array formulas carry out calculations, such as addition and multiplication, on the values in one or more arrays rather than a single data value. Arrays have much in common with standard formulas. Arrays and formulas follow the same syntax rules, use the same mathematical operators, and follow the same order of operations.
Types of Arrays
There are two types of arrays used in spreadsheet apps:
One-Dimensional Array (also known as a vector or vector array): Data is located in a single row (one-dimensional horizontal array) or in a single column (one-dimensional vertical array). Two-Dimensional Array (also known as a matrix): Data is located in multiple columns or rows.
Array (CSE) Formulas in Excel
In Excel, array formulas are surrounded by curly braces " { } “. These braces cannot be typed. The braces must be added to a formula by pressing Ctrl+Shift+Enter after typing the formula into a cell or cells. This is why array formulas are called CSE formulas in Excel.
Create a Basic Array Formula
In the following example, the formula will be surrounded by curly braces and each cell holding the formula will contain a different result. This denotes an array has been successfully created.
Enter the data in a blank worksheet. Enter the data in columns D and E to follow along with this tutorial. Enter the formula for your array. To follow along with this example, select cell F1 and type =D1:D3*E1:E3 Do not press Enter at the end of the formula. Press and hold the Ctrl and Shift keys. Press the Enter key. Release the Ctrl and Shift keys. The result appears in cell F1 and the array appears in the Formula Bar.
Different Types of Array Formulas
Multi-Cell Array Formulas
Multi-cell array formulas are located in multiple worksheet cells and return an array as an answer. In other words, the same formula is located in two or more cells and returns different answers in each cell.
Each copy or instance of the array formula performs the same calculation in each cell it is located in. But, because each instance of the formula uses different data in its calculations, each instance produces different results.
Here’s an example of a multiple cell array formula:
Single Cell Array Formulas
Single cell array formulas uses a function (such as SUM, AVERAGE, or COUNT) to combine the output of a multi-cell array formula into a single value in a single cell.
Here’s an example of a single cell array formula:
