Create an Array Formula

Array formulas enable you to make multiple calculations with a single formula. There are two types of array formulas: multi-cell and single cell. A formula that performs multiple calculations and returns multiple results is called a multi-cell formula. A formula that performs multiple calculations and returns a single result is called a single cell formula.

You start a multi-cell formula by selecting the cells where the results will appear. When you complete your entry, you press Ctrl + Shift + Enter. Excel places curly braces around your entry. The curly braces distinguish your entry as an array formula and must be created by pressing Ctrl + Shift + Enter.

Let's take a look at a multi-cell array formula and see what it does.

Formula: {=A1:C1*A2:C2}

This formula takes the value in cell A1, multiplies it by the value in cell A2, and places the result, 6, in cell A3. Takes the value in cell B1, multiplies it by the value in cell B2, and places the result, 15, in cell B3. Then takes the value in cell C1, multiplies it by the value in cell C2, and places the result, 12, in cell C3.

Enter a Multi-cell Array Formula

1. Select the cells where you want the results to appear.

2. Enter the formula.

3. Press Ctrl + Shift + Enter. Excel calculates the results and places them in the cells you selected.

With a multi-cell array formula, several cells share the same formula. As a result, within a range that contains a multi-cell array formula you cannot:

• Change the contents of a cell
• Clear, move, or delete a cell
• Insert cells

If you attempt any of these actions, you will receive an error message.

You start a single-cell formula by selecting one cell. As with a multi-cell formula, you end the formula by pressing Ctrl + Shift + Enter. Like a multi-cell formula, a single cell formula will perform multiple calculations, but it returns a single result.

Let's take a look at a single-cell array formula and see what it does.

Formula: {=SUM(A1:C1*A2:C2)}

This formula takes the value in cell A1, multiplies it by the value in cell A2, the value in cell B1, multiplies it by the value in cell B2, the value in cell C1, multipies it by the value in cell C2,  adds the results of those calculations and returns the result.

Enter a Single-cell Array Formula

1. Select the cell where you want the result to appear.
2. Enter the formula.
3. Press Ctrl + Shift + Enter. Excel calculates the result and places it in the cell you selected.

Edit an array formula

1. Double-click in the cell in which you entered the formula.
2. Use the Formula Bar to edit.

Or

1. Click anywhere in the range that contains the formula.
2. Choose the Home tab.
3. Click Find and Select in the Editing group. A menu appears.
4. Click Go To. The Go To dialog box opens.
5. Click the Special button.
6. Click Current Array.
7. Click OK. You can now use the Formula Bar to edit.