Create an array formula

When you enter an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.), Microsoft Excel automatically inserts the formula between { } (braces).

ShowCalculate a single result

You can use an array formula to perform several calculations to generate a single result. This type of array formula can simplify a worksheet model by replacing several different formulas with a single array formula.

  1. Click the cell in which you want to enter the array formula.
  2. Type the array formula.

For example, the following calculates the total value of an array of stock prices and shares, without using a row of cells to calculate and display the individual values for each stock.

Array formula that produces a single result

Array formula that produces a single result

When you enter the formula {=SUM(B2:C2*B3:C3)} as an array formula, it multiples the Shares and Price for each stock, and then adds the results of those calculations together to get a total value of 9500.

  1. Press CTRL+SHIFT+ENTER.

ShowCalculate multiple results

Some worksheet functions return arrays of values, or require an array of values as an argument. To calculate multiple results with an array formula, you must enter the array into a range of cells that has the same number of rows and columns as the array arguments have.

  1. Select the range of cells in which you want to enter the array formula.
  2. Type the array formula.

For example, given a series of three sales figures (column B) for a series of three months (column A), the TREND function determines the straight-line values for the sales figures. To display all of the results of the formula, it is entered into three cells in column C (C1:C3).

Array formula that produces multiple results

Array formula that produces multiple results

When you enter the formula =TREND(B1:B3,A1:A3) as an array formula, it produces three separate results (22196, 17079, and 11962), based on the three sales figures and the three months.

  1. Press CTRL+SHIFT+ENTER.
 
 
Applies to:
Excel 2003