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

In Excel, the Name Box refers to an input box directly to the left of the formula bar . The Name Box normally displays the address of the “active cell” on the worksheet. You can also use the name box to quickly create a named range.

Another use for the Name Box is to navigate quickly to any range in a worksheet. If you type Z100 into the Name Box, the active cell will move to that address. If you type A1:A10 into the Name Box, that range will be selected.

Finally, if you have one or more named ranges in a workbook, the Name Box behaves like a drop down menu. You can select and navigate quickly to any name.