An array is a range or group of related data values. The values in an array are normally stored in adjacent cells. Learn how arrays are used in formulas and as arguments for specific functions, such as the array forms of the LOOKUP and INDEX functions.

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.

These instructions apply to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, Excel for Microsoft 365, and Excel Online.

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.

An exception to this rule is when curly braces are used to enter an array as an argument for a function that normally contains just a single value or cell reference.

  • 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

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.

When an array formula is edited, the curly braces disappear from around the array formula. To get them back, press Ctrl+Shift+Enter after you’ve 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.

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:

Get the Latest Tech News Delivered Every Day