Use array constants in array formulas

When you enter an array formula, you most often use a range of cells in your worksheet, but you don't have to. You can also use array constants, values you just enter in the formula bar inside braces: {}. Then you can name your constant so it's easier to use again.

You can use constants in your array formulas or by themselves.

  1. In your array formula, type an opening brace, the values you want, and a closing brace. Here's an example: =SUM(A1:E1*{1,2,3,4,5})

    The constant is inside the braces ({)}, and yes, you really do type those braces manually.

  2. Enter the rest of your formula and press Ctrl+Shift+Enter.

The formula will look something like {=SUM(A1:E1*{1,2,3,4,5})}, and the results will look like this:


An array constant nested in a SUM formula

The formula multiplied A1 by 1 and B1 by 2, etc., saving you from having to put 1,2,3,4,5 in cells on the worksheet.

Use a constant to enter values in a column

To enter values in a single column, such as 3 cells in column C, you:

  1. Select the cells you want to use.

  2. Enter an equal sign and your constant. Separate the values in the constant with semicolons, not commas, and if you're entering text, surround it with double quotes. For example: ={"Quarter 1";"Quarter2";"Quarter 3"}

  3. Press Ctrl+Shift+Enter. The constant looks like this:

A vertical array constant that uses text

In geek terms, this is a one-dimensional vertical constant.

Use a constant to enter values in a row

To quickly enter values in a single row, such as cells F1, G1, and H1, you:

  1. Select the cells you want to use.

  2. Enter an equal sign and your constant, but this time you separate the values with commas, not semicolons. For example: ={1,2,3,4,5}

  3. Press Ctrl+Shift+Enter, and the constant looks like this:

A one-dimensional horizontal array constant

In geek terms, this is a one-dimensional horizontal constant.

Use a constant to enter values in multiple columns and rows

  1. Select the cells you want.

Make sure the number of rows and columns you select matches the number of values in your constant. For example, if your constant will write data to four columns and three rows, select that many columns and rows.

  1. Enter an equal sign and your constant. In this case, separate the values in each row with commas, and use a semicolon at the end of each row. For example:

    ={1,2,3,4;5,6,7,8;9,10,11,12}

  2. Press Ctrl+Shift+Enter, and:

A two-dimensional array constant

In geek terms, this is a two-dimensional constant because it fills columns and rows. If you're wondering, you can't create a three-dimensional constant, meaning you can't nest a constant inside another one.

Give it a try

If you want to play around with array constants before you try them out with your own data, you can use the sample data here.

The workbook below shows examples of array formulas. To best work with the examples, you should download the workbook to your computer by clicking the Excel icon in the lower-right corner, and then open it in the Excel desktop program.

Copy the table below and paste it into Excel in cell A1. Be sure to select cells E2:E11, enter the formula =C2:C11*D2:D11, and then press Ctrl+Shift+Enter to make it an array formula.

Salesperson

Car Type

Number Sold

Unit Price

Total Sales

Barnhill

Sedan

5

2200

=C2:C11*D2:D11

Coupe

4

1800

Ingle

Sedan

6

2300

Coupe

8

1700

Jordan

Sedan

3

2000

Coupe

1

1600

Pica

Sedan

9

2150

Coupe

5

1950

Sanchez

Sedan

6

2250

Coupe

8

2000

The formula in cells E2:E11 multiplies the array in C2:C11 by the array in D2:D11, and returns the results in the E column for each row. This is an array formula, so to create from scratch, you'd select E2:E11, type the formula =C2:C11*D2:D11, and press Ctrl+Shift+Enter (not just Enter).

Also, here's a suggestion: Read how to name an array constant, and name the constants as you go. They're easier to reuse. Here are some things you can try in Excel:

  1. Select cells A1 through D1, copy and paste this formula into the formula bar, and press Ctrl+Shift+Enter:

    ={1,2,3,4}

    The formula creates a horizontal array.

  2. Select cells A3 through A7, copy and paste this formula into the formula bar, and press Ctrl+Shift+Enter:

    ={6;7;8;9;10}

    The formula creates a vertical array, a set of values in a column.

  3. Select cells A10 through D12, copy and paste this formula into the formula bar, and press Ctrl+Shift+Enter:

    ={1,2,3,4;5,6,7,8;9,10,11,12}

    The formula creates a two-dimensional array. Notice how the semicolon marks the end of each row.

Use a constant in a formula

Now that you're familiar with array constants, here's a working example.

  1. In any blank cell, enter (or copy and paste) this formula, and then press Ctrl+Shift+Enter:

    =SUM(A1:E1*{1,2,3,4,5})

The value 85 appears in cell A3.

What happened? You multiplied the value in A1 by 1, the value in cell B2 by 2, and so on, then the SUM function added those results. You could also enter the formula as =SUM(A1*1,B1*2,C1*3,D1*4,E1*5)

If you wanted to, you could enter both sets of values as array constants:

=SUM({3,4,5,6,7}*{1,2,3,4,5})

To try this, copy the formula, select a blank cell and paste the formula into the formula bar, and then press Ctrl+Shift+Enter. You see the same result.

Notes: Look for these problems if your constants don't work:

  • Make sure you separate your values with the proper character. If you omit a comma or semicolon, or if you put one in the wrong place, the array constant may not look right or you may see a warning message.

  • You may have selected a range of cells that doesn't match the number of elements in your constant. For example, if you select a column of six cells for use with a five-cell constant, the #N/A error appears in the empty cell. If you don't select enough cells, Excel omits the values that don't have a corresponding cell.

  • For more about array formulas:

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!

×