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

Create an array formula that calculates multiple results

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.

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

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.

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

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×