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

Support Exceljet

Many websites (including Exceljet) have lost over half their traffic in the past year because of the crushing impact of AI on web searches. Exceljet is still at it, publishing free content each week. If you find Exceljet helpful, you can support the site and go ad-free with the Exceljet Lifetime Pass. One payment, no subscription, and ads are gone forever. Plus, you get our Excel Shortcuts video course as a thank you.

Get the Lifetime Pass