Quick, clean, and to the point

Multi-cell array formula

Example of multi-cell array formula

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:


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


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 + /