How to Use Array Constants in Excel

Array constants can be used to create arrays, or to modify existing arrays in a worksheet.

The worksheet is set up for a vertical array and a horizontal array. First, we’ll use array constants to fill in the x column of the vertical array. Select the cells in this column. An array is designated with a curly bracket { after the equals sign. In a vertical array, the constants are separated by semicolons.

Type into the formula bar:

={1;2;3;4}

Type Ctrl-Shift-Enter to fill in the vertical array with those constants:

If you forget and use Enter instead of Ctrl-Shift-Enter, the formula won’t be applied to the whole array.

To fill in the x² column, we can perform an operation as in the previous section. To do this, select the empty cells in the x² column. In the formula bar, enter an equals sign, then select the x values in column B. Add ^2 to raise it to the second power:

=B5:B8^2

Then type Ctrl-Shift-Enter. No curly brackets are needed for this method.

However, it’s possible to use array constants to fill in the x² column and the x³ column together, with a single array formula.

First, select all the cells to populate. Go to the formula bar and enter an equals sign. Select the x values, and add a carat (^). So far you should have:

=B5:B8^

Next, we’ll add the exponents as a horizontal array. In a horizontal array, the values are separated with commas.

=B5:B8^{2,3}

Type Ctrl-Shift-Enter, and the formula will populate the other two columns.

There is a similar table in a horizontal orientation. To fill in the x values, select the empty cells, then enter into the formula bar:

={1,2,3,4}

Then type Ctrl-Shift-Enter. Remember to separate the values with commas to obtain a horizontal array.

To fill in the remainder of the horizontal array, select the remaining empty cells. In the formula bar, type:

    =G4:J4^{2;3}

Remember to use a semicolon to create the vertical component of this array. Again, type Ctrl-Shift-Enter to enter it as an array formula.