Name an array constant

When you use an array constant in an array formula, you can give it a name, and then you can reuse it easily.

1. Click Formulas > Define Name.

2. In the Name box, enter a name for your constant.

3. In the Refers to box, enter your constant. For example, you can use ={"January","February","March"}.

   The dialog box should look something like this:

   

4. Click OK.

5. In your worksheet, select the cells that will contain your constant.

6. In the formula bar, enter an equal sign and the name of the constant, such as =Quarter1.

7. Press Ctrl+Shift+Enter.

Here's how the example looks when you're done:

Notes: 

• When you use a named constant as an array formula, remember to enter the equal sign. If you don't, Excel interprets the array as a string of text and you see an error message.

• You can use numbers, text, logical values (such as TRUE and FALSE), and error values (such as #N/A) in your constants. You can also use numbers in the integer, decimal, and scientific formats. If you include text, you surround it with double quotes ("").

• Array constants can't contain other arrays, formulas, or functions. In other words, they can contain only text, numbers, or characters separated by commas or semicolons. Excel displays a warning message when you enter a constant such as {1,2,A1:D4} or {1,2,SUM(Q2:Z8)}. Also, numbers can't contain percent signs, dollar signs, commas, or parentheses.

More about array formulas

• Create an array formula

• Expand an array formula

• Delete an array formula

• Rules for changing array formulas

Top of Page