Microsoft Office Tutorials: Name an array constant

Sunday, July 4, 2021

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

Click Formulas > Define Name.
In the Name box, enter a name for your constant.
In the Refers to box, enter your constant. For example, you can use ={"January","February","March"}.

The dialog box should look something like this:
Click OK.
In your worksheet, select the cells that will contain your constant.
In the formula bar, enter an equal sign and the name of the constant, such as =Quarter1.
Press Enter if you have a current Microsoft 365 subscription. Otherwise, press Ctrl+Shift+Enter.

Note: If you have a current version of Microsoft 365, then you can simply enter the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by first selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you. For more information on array constants in array formulas, see the section Naming array constants in Guidelines and examples of array formulas.

Here's how a dynamic array will look like in Microsoft 365:

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.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.