Perform calculations on data

Create array formulas

Most Excel formulas calculate values to be displayed in a single cell. For example, you could add the formulas =B1*B4, =B1*B5, and =B1*B6 to consecutive worksheet cells to calculate shipping insurance costs based on the value of a package’s contents.

A worksheet with data to be summarized by an array formula

Instead of entering the same formula in multiple cells one cell at a time, you can enter a formula in every cell in the target range at the same time by creating an array formula. To calculate package insurance rates by multiplying the values in the cell range B4:B6 by the insurance rate in cell B1, you select the target cells (C4:C6) and enter the formula =B1*B4:B6. Note that you must select a range of the same shape as the values you’re using in the calculation. (For example, if the value range is three columns wide by one row high, the target range must also be three columns wide by one row high.) If you enter the array formula into a range of the wrong shape, Excel displays duplicate results, incomplete results, or error messages, depending on how the target range differs from the value range.

When you press Ctrl+Shift+Enter, Excel creates an array formula in the selected cells. The formula appears within a pair of braces to indicate that it is an array formula.

An array formula calculates multiple results

To create an array formula

1. Select the cells in which you want to display the formula results.

2. In the formula bar, enter the array formula.

3. Press Ctrl+Shift+Enter.

To edit an array formula

1. Select every cell that contains the array formula.

2. In the formula bar, edit the array formula.

3. Press Ctrl+Shift+Enter to re-enter the formula as an array formula.