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.
-
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.
-
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:
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:
-
Select the cells you want to use.
-
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"}
-
Press Ctrl+Shift+Enter. The constant looks like this:
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:
-
Select the cells you want to use.
-
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}
-
Press Ctrl+Shift+Enter, and the constant looks like this:
In geek terms, this is a one-dimensional horizontal constant.
Use a constant to enter values in multiple columns and rows
-
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.
-
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}
-
Press Ctrl+Shift+Enter, and:
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.
Use a constant in a formula
Now that you're familiar with array constants, here's a working example.
-
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:
