Excel Training & Excel Tutorial

Here’s a simple example that illustrates how array formulas work. In the Expenses workbook shown, the 2008 BUDGET totals are calculated using a separate formula for each month, as shown here:
January 2008 BUDGET =C11*$C$3
February 2008 BUDGET =D11*$C$3
March 2008 BUDGET =E11*$C$3


You can replace all three formulas with a single array formula by following these steps:

1. Select the range that you want to use for the array formula. In the 2008 BUDGET example, you’d select C13:E13.
2. Type the formula and, in the places where you would normally enter a cell reference, type a range reference that includes the cells you want to use. Don’t—I repeat, don’t— press Enter when you’re done. In the example, you’d enter =C11:E11*$C$3.
3. To enter the formula as an array, press Ctrl+Shift+Enter. The 2008 BUDGET cells (C13, D13, and E13) now all contain the same formula: {=C11:E11*$C$3}