A multi-cell array formula is an array formula that returns multiple results to more than one cell at the same time. In the example shown, the formula in B3:B12 is:

{=ROW(1:10)}

Here, the ROW function returns an array with 10 items:

{1;2;3;4;5;6;7;8;9;10}

and each item in this array is displayed in a different cell.

Steps to enter a multi-cell array formula

To enter a multi-cell array formula, follow these steps:

  1. Select multiple cells (cells that will contain the formula)
  2. Enter an array formula in the formula bar
  3. Confirm formula with Control + Shift + Enter

Multi-cell array formulas have unique characteristics:

  1. All cells display the same formula (relative references don't change)
  2. You can't insert cells into the range that contains a multi-cell array formula
  3. You must select the full range in order to edit the formula
  4. You must confirm changes with control + shift + enter

To select all the cells in a multi-cell array formula, you can use the keyboard shortcut control + /

Terms