Constructing an array in excel is easy. Type ={1,2,3} in the formula bar and hold and press CTRL + SHIFT + Enter. Make sure the formula bar looks like this:

excel arrays

The formula must have a beginning and ending curly bracket, you can´t enter those yourself. If your formula bar looks like the picture above then you have made your first array!

Why is there only one value shown? I made 3 values (1 2 and 3). There can only be one value in each cell. To be able to see all values enter the formula in cell range A1:C1

  1. Select cell range A1:C1
  2. Click in formula bar
  3. Press and hold CTRL + SHIFT
  4. Press Enter
  5. Release all keys

excel arrays1

You have now extended the formula to three cells and all values in the array are visible on the sheet, see picture above.

Arranging constants in a formula

You can construct arrays with a comma delimiter and a semicolon delimiter. What is the difference? Use the comma when you want values horizontally and semicolon vertically. Look at the formula bar in this animated picture:

excel arrays

You can also combine colons and semicolons. ={1,2;3,4} A comma tells excel that the next value is to the right of the first value. A semicolon puts the next value on the next row.

excel arrays2

I try to enter an array with text values but it doesn't work, why? Use double quotes around your values, like this: ={"A","B"}

Cell references in formulas

All arrays presented above contains constants, meaning they don´t change unless you edit them. This formula =A1:A10 contains a cell reference to A1:A10. The values in this formula change if you change a value in cell range A1:A10. Formula =A1:A10 does not contain any constants.

This means that you can use values on your sheet in your array formula. This is extremely useful.

a cell ref

Quickly build an array

I want to construct an array from 1 to 1000? I don´t want to enter all these values. You don´t have to, the ROW function can do that for you. =ROW(1:1000) creates an array from 1 to 1000.

Hard code values

Can I hard code values from 1 to 1000 in a formula without entering them myself? Yes, you can. Type =ROW(1:1000) in the formula bar and press F9.

Excel converts =ROW(1:1000) to ={1,2,3, ... , 1000)

Is Ctrl + Shift + Enter always necessary?

Can I build an array formula without entering it as an array formula?
Yes, you can. Read this post:  No more array formulas?

Can I enter an array formula in merged cells?

Excel tells me "Array formulas are not valid in merged cells", how can I
You can enter array formulas in merged cells: How to enter array formulas in merged cells

Why are arrays so powerful?

Why would you want to construct arrays? You can do seriously complicated calculations, excel is really powerful.

Alright, show me some complicated array formulas? 

Cool, even more complicated calculations?