More arrays: Introducing array constants in Excel

Power User Corner

 By John Walkenbach
 Adapted for Office Online by Colin Wilcox

 

Feeling comfortable with array formulas since reading the last column? This time, we'll look at a component of array formulas called array constants—a type of array that Excel stores in memory instead of in a workbook. This is part two of a two-column series.

Applies to
Microsoft Office Excel 2003
Excel 2002

The previous Power User column, Introducing Array Formulas in Excel, explained how to enter and edit single-cell and multi-cell array formulas. This column explains how to use a component of array formulas called array constants: arrays that Excel stores and uses in memory instead of in a workbook.

You create and use array constants whenever you need to add sets of values that don't change (such as month names or pi) to your array formulas. Constants process faster because they reside in memory. They also reduce clutter in your workbooks because, well, they reside in memory.

A brief introduction to array constants

Array constants are a component of array formulas. You create array constants by entering a list of items and then manually (yes, manually) surrounding the list with braces ({ }), like so:

={1,2,3,4,5}

In the first column, we emphasized the need to press CTRL+SHIFT+ENTER when you create array formulas. Because array constants are a component of array formulas, you surround the constants with braces manually. You then use CTRL+SHIFT+ENTER to enter the entire formula.

If you delimit (separate) the items with commas, you create a horizontal array (a row). If you delimit the items with semicolons, you create a vertical array (a column). To create a two-dimensional array, you delimit the items in each row with commas, and you delimit each row with a semicolon.

Like array formulas, you can use array constants with any of the built-in functions that Excel provides. The following sections explain how to create each kind of constant and how to use them with functions in Excel.

Create one-dimensional and two-dimensional constants

At this point, a little practice creating horizontal, vertical, and two-dimensional constants won't hurt. Follow these steps.

To create a horizontal constant

  1. Use the workbook from the previous column, or start a new workbook.
  2. Select cells A1 through E1.
  3. In the formula bar, enter the following formula and press CTRL+SHIFT+ENTER:

={1,2,3,4,5}

You see the following result.

A horizontal array constant in a formula

You're probably wondering why you can't just type the numbers manually. Keep going, because the Use constants in formulas section later in this column demonstrates the advantages of using array constants.

To create a vertical constant

  1. In your workbook, select a column of five cells.
  2. In the formula bar, enter the following formula and press CTRL+SHIFT+ENTER:

={1;2;3;4;5}

You see the following result.

A vertical array constant in an array formula

To create a two-dimensional constant

  1. In your workbook, select a block of cells four columns wide by three rows high.
  2. In the formula bar, enter the following formula and press CTRL+SHIFT+ENTER:

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

You see the following result.

A two-dimensional array constant in an array formula

The next section shows you how to put array constants to use.

Use constants in formulas

Let's start with a simple example that uses what we've discussed:

  1. Open a blank workbook.
  2. Copy the following table starting at cell A1. Use the Paste Options button Paste Options button to match the destination formatting.
3 4 5 6 7
  1. In cell A3, enter the following formula and press CTRL+SHIFT+ENTER: =SUM(A1:E1*{1,2,3,4,5})

Notice that Excel surrounds the constant with another set of braces, because you entered it as an array formula.

An array formula with an array constant

Also, the value 85 appears in cell A3. The next section explains how the formula works.

A look at the array constant syntax

The formula you just used contains several parts.

Syntax of an array formula with an array constant

The last element inside the parentheses is the array constant: {1,2,3,4,5}. Remember that Excel does not surround array constants with braces. That's your job. Also, remember that after you add a constant to an array formula, you press CTRL+SHIFT+ENTER to enter the formula.

Because Excel performs operations on parentheses first, the next two elements that come into play are the values stored in the workbook (A1:E1) and the operator. At this point, the formula multiplies the values in the stored array by the corresponding values in the constant. It's the equivalent of: =SUM(A1*1,B1*2,C1*3,D1*4,E1*5)

Finally, the SUM function adds the values, and the sum appears in cell A3:

85.

To avoid using the stored array and just keep the operation entirely in memory, replace the stored array with another array constant:

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

Try it. Copy the function, select a blank cell in your workbook, paste the formula into the Formula Bar, and then press CTRL+SHIFT+ENTER. You see the same result as you did in the earlier exercise.

Elements you can use in constants

Array constants can contain numbers, text, logical values such as TRUE and FALSE, and error values such as #N/A. You can use numbers in the integer, decimal, and scientific formats. If you include text, you must surround that text with double quotation marks.

Array constants can't contain additional arrays, formulas, or functions. In other words, they can only contain text or numbers separated by commas or semicolons. Excel gives you an error message when you use something like {1,2,A1:D4} or {1,2,SUM(Q2:Z8)}. Also, numeric values can't contain percent signs, dollar signs, commas, or parentheses.

Naming array constants

Possibly the best way to use array constants is to name them. Named constants can be much easier to use, and they can hide some of the complexity of your array formulas from beginning users.

To name an array constant and use it in a formula

  1. On the Insert menu in Excel, point to Name, and then click Define. The Define Name dialog box appears.
  2. In the Names in workbook box, enter Quarter1.
  3. In the Refers to box, enter the following constant (remember to type the braces manually): ={"January","February","March"}

The dialog box should look like this:

The Define Name dialog box with formula

  1. Click OK.
  2. In your workbook, select a row of three blank cells.
  3. Enter the following formula, and then press CTRL+SHIFT+ENTER:

=Quarter1

You see the following result.

A named array entered as a formula

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. Finally, keep in mind that you can use combinations of text and numbers.

Troubleshooting array constants

Look for the following problems when your array constants don't work:

  • Separate all elements with the proper character. If you omit a comma or semicolon, or if you put one in the wrong place, Excel complains.
  • Select a range of cells that matches 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 value appears in the empty cell. Conversely, if you select too few cells, Excel just omits the values that don't have a corresponding cell.

Array constants in action

The following examples demonstrate a few of the ways in which you can put array constants to use in array formulas. Some of the examples use the TRANSPOSE function to convert rows to columns and vice versa. For more about using the function, see the TRANSPOSE function reference topic.

To multiply each item in an array

  1. Select a block of empty cells four columns wide by three rows high.
  2. Enter the following formula, and then press CTRL+SHIFT+ENTER:

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

To square the items in an array

  • You can do the following:

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

Or you can make your life easier:

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

To transpose a one-dimensional row

  1. Select a column of five blank cells.
  2. Enter the following formula, and then press CTRL+SHIFT+ENTER:

=TRANSPOSE({1,2,3,4,5})

Even though you enter a horizontal array constant, the TRANSPOSE function converts the constant into a column.

To transpose a one-dimensional column

  1. Select a row of five blank cells.
  2. Enter the following formula, and then press CTRL+SHIFT+ENTER:

=TRANSPOSE({1;2;3;4;5})

Even though you enter a vertical array constant, the TRANSPOSE function converts the constant into a row.

To transpose a two-dimensional constant

  1. Select a block of cells three columns wide by four columns high.
  2. Enter the following constant, and press CTRL+SHIFT+ENTER.

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

The function converts each row into a series of columns.

More examples

To see examples of array formulas and array constants in action, see the reference pages Putting Basic Array Formulas to Work and Putting Advanced Array Formulas to Work.

More information


About the authors

  • John Walkenbach is an Excel Most Valuable Professional (MVP) and author of Excel 2002 Formulas, the book that provides the information for this set of columns. To learn more about John's other books, see his book page.
  • Colin Wilcox writes for the Office Help team. In addition to contributing to the Office Power User Corner column, Colin writes articles and tutorials for Microsoft Data Analyzer.