## Learn the basics of Excel arrays

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:

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

- Select cell range A1:C1
- Click in formula bar
- Press and hold CTRL + SHIFT
- Press Enter
- Release all keys

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:

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.

*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.

### 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?*

### 2 Responses to “Learn the basics of Excel arrays”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

First, this is the greatest site I ever found regarding excel tips and tricks and the way you explain your solutions!! My question: I have noticed that array formulas are very calculation intensive, so when you have 500 or more lines, calculating the sheet gets slow. What is your experience or recommendation regarding the size limits of using array formulas efficiently? I did not find anything posted... (I work with sheets that have tens of thousands of lines)

[…] Diagramme wirken mitunter überladen und sind deshalb schwer zu deuten. Autor Alexander Wildt zeigt, wie mit Hilfe von Steuerelementen Säulendiagramme in verschiedenen Perspektiven dargestellt werden können. Get-Digital-Help 07.09.2015 „Learn the basics of Excel arrays“ http://www.get-digital-help.com/2015/09/07/learn-the-basics-of-excel-arrays/ […]