Home > Sample chapters

Perform calculations on data

Use 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.

p0092_01.jpg

A worksheet with data to be summarized by an array formula.

Rather than add the same formula to multiple cells one cell at a time, you can add a formula to every cell in the target range at the same time by creating an array formula. To create an array formula, you enter the formula’s arguments and press Ctrl+Shift+Enter to identify the formula as an array formula. To calculate package insurance rates for values in the cell range B4:B6 and the rate in cell B1, you would select a range of cells with the same shape as the value range and enter the formula =B1*B4:B6. In this case, the values are in a three-cell column, so you must select a range of the same shape, such as C4:C6. 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.

p0092_02.jpg

A worksheet with an array formula ready to be entered.

In addition to creating an array formula that combines a single cell’s value with an array, you can create array formulas that use two separate arrays. For example, a company might establish a goal to reduce sorting time in each of four distribution centers. This worksheet stores the previous sorting times in minutes in cells B2:B5, and the percentage targets in cells C2:C5. The array formula to calculate the targets for each of the four centers is =B2:B5*C2:C5 which, when you enter it into cells D2:D5 by pressing Ctrl+Shift+Enter, would appear as {= B2:B5*C2:C5}.

To edit an array formula, you must select every cell that contains the array formula, click the formula bar to activate it, edit the formula in the formula bar, and then press Ctrl+Shift+Enter to re-enter the formula as an array formula.

To create or edit an array formula

  1. Select the cells to include in the array formula.

  2. Enter or edit your array formula.

  3. Press Ctrl+Shift+Enter.