Create an array formula

Also referred to as Ctrl Shift Enter or CSE formulas because you need to press Ctrl+Shift+Enter to enter them in your worksheet, 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.)s are very powerful formulas that enable you to perform calculations you can’t do with standard worksheet functions. Use an array formula to do the seemingly impossible, such as:

  • Count the number of characters in a range of cells.
  • Sum numbers that meet certain conditions, such as the lowest values in a range or numbers that fall between an upper and lower boundary.
  • Sum every nth value in a range of values.

Excel provides two types of array formulas: Array formulas that perform several calculations to generate a single result and array formulas that calculate multiple results. Some worksheet functions return arrays of values, or require an array of values as an argument. For more information, see Guidelines and examples of array formulas.

What do you want to do?


Create an array formula that calculates 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. Enter the formula that you want to use.

Array formulas use standard formula syntax. They all begin with an equal sign (=), and you can use any of the built-in Excel functions in your array formulas.

For example, the following formula calculates the total value of an array of stock prices and shares, without using a row of cells to calculate and display the total 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, Excel multiples the number of shares by the price for each stock (500*10 and 300*15), and then adds the results of those calculations together to get a total value of 9500.

  1. Press Ctrl+Shift+Enter.

Excel automatically inserts the formula between { } (a pair of opening and closing braces).

 Note   Manually typing braces around a formula will not convert it into an array formula — you must press Ctrl+Shift+Enter to create an array formula.

 Important   Any time you edit the array formula, the braces ({ }) disappear from the array formula, and you must press Ctrl+Shift+Enter again to incorporate the changes into an array formula and to add the braces.

Top of Page Top of Page

Create an array formula that calculates multiple results

To calculate multiple results by using an array formula, enter the array into a range of cells that has the exact same number of rows and columns that you’ll use in the array arguments.

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

Array formulas use standard formula syntax. They all begin with an equal sign (=), and you can use any of the built-in Excel functions in your array formulas.

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

Excel automatically inserts the formula between { } (a pair of opening and closing braces).

 Note   Manually typing braces around a formula will not convert it into an array formula — you must press Ctrl+Shift+Enter to create an array formula.

 Important   Any time you edit the array formula, the braces ({ }) disappear from the array formula, and you must press Ctrl+Shift+Enter again to incorporate the changes into an array formula and to add the braces.

Top of Page Top of Page

 
 
Applies to:
Excel 2013