Microsoft Office Tutorials and References

In Depth Information

**Using array formulas**

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

3

IMPORTANT
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 curly braces to indicate it is an array formula. In this case,

the formula in cells C4:C6 is
{=B1*B4:B6}
.

IMPORTANT
You can’t add curly braces to a formula to make it an array formula—you must

press Ctrl+Shift+Enter to create it.

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, Consolidated

Messenger might establish a goal to reduce sorting time in each of four distribution

centers.