How Arrays, Array Formulas, and Table Arrays Are Used in Excel.

Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs.

The values in an array are normally stored in adjacent cells.

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 and formulas follow the same syntax rules, use the same mathematical operators, and follow the same order of operations.

Two-Dimensional Array (also known as a matrix): Data is located in multiple columns or rows.

The braces must be added to a formula by pressing Ctrl Shift Enter after typing the formula into a cell or cells.

In the following example, the formula will be surrounded by curly braces and each cell holding the formula will contain a different result.

Enter the data in columns D and E to follow along with this tutorial.

To follow along with this example, select cell F1 and type D1:D3E1:E3.

The result appears in cell F1 and the array appears in the Formula Bar.

To get them back, press Ctrl Shift Enter after youve made the changes to the formula.

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.

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.

Excel For Mac Formulas