## Learn the basics of Excel arrays

* Why are array formulas so powerful?*They allow you to do things not possible with regular formulas, an array formula can do multiple calculations on several values simultaneously. You can do seriously complicated calculations, Excel is definitively a powerful tool.

**How do I start?**

Constructing an array in excel is easy. Double click on cell A1 and type ={1,2,3} in the formula bar and hold and press CTRL + SHIFT simultaneously, then press Enter once. Release all keys.

The formula must have a beginning and ending curly bracket. Don't enter those characters yourself. Make sure the formula bar looks like this: {={1,2,3}}

If your formula bar looks like the picture above then you have made your first array!

** Why is only one value shown? I have 3 values in the array? **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 simultaneously
- Press Enter
- Release all keys

The animated picture above shows you how to extend an array formula from cell A1 to A1:C1, this allows you to see all values in the array.

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 often can. However it makes the formula more complicated. 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

### Are there any exceptions?

Yes, both the SUMPRODUCT function and the LOOKUP function lets you build arrays without requiring you to enter it as an array formula.

**How to use Excel SUMPRODUCT function**

Comments(2) Filed in category: Excel, SUMPRODUCT function

Comments(0) Filed in category: Excel, Lookup function

The INDEX function lets you encapsulate an array so you don't need to enter it as an array formula:

Comments(7) Filed in category: Excel, Unique distinct values, Vlookup

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

### Any caveats?

A major disadvantage with array formulas is that if you double click on a cell containing an array formula and then clicks on another cell, the array formula changes to a regular formula. Beginner Excel users may not know about this and could possibly break your worksheet calculations.

However, this is not the case if you press Escape key to exit editing an array formula or if you press CTRL + SHIFT + Enter.

Array formulas can slow down your worksheet calculations.

**Alright, show me some useful array formulas?**

**Alright, show me some useful array formulas?**

**5 easy ways to extract unique distinct values**

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values, Unique values

**5 easy ways to VLOOKUP and return multiple values**

Comments(445) Filed in category: Excel, VLOOKUP and return multiple values

Comments(14) Filed in category: Excel, Sequence

*Cool, even more complicated calculations?*

**Count multiple text strings in a cell range**

Comments(0) Filed in category: Count values, Excel

Comments(7) Filed in category: Count values, Excel, MMULT function, SUM function

Comments(13) Filed in category: Excel, MMULT function, Permutations, SUM function

**Count overlapping days across multiple date ranges**

Comments(1) Filed in category: Excel, MMULT function, Overlapping

### Category: Count values

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. Tip! I highly […]Comments(93) Filed in category: Count unique distinct values, Count values, Excel

Count unique distinct values in a column

Question: I have a list of values (A1:A6), how do I count unique distinct values? Answer: First, let me explain […]Comments(26) Filed in category: Count unique distinct values, Count values, Excel

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]Comments(26) Filed in category: Conditional formatting, Count values, Excel

Count number of times a string exist in multiple cells using excel formula

Question: How do I find the number of occurances a word exists in a range of cells? It does not […]Comments(19) Filed in category: Count text values, Count values, Excel

Excel: List intervals between two values

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]Comments(16) Filed in category: Count values, Excel, Range

Comments(16) Filed in category: Count values, Excel, Frequency

Count unique distinct values in two columns in excel

Question: I have values in two not adjacent columns. I want to know how many unique distinct values there are […]Comments(14) Filed in category: Count unique distinct values, Count values, Excel

Count records between two dates with multiple parameters in excel

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]Comments(14) Filed in category: Count values, Dates, Excel

Count text that occurs multiple times in excel cell

Question: How do I count how many times a text string occurs in a cell in excel? Answer: Formula in […]Comments(9) Filed in category: Count text values, Excel

Count occurences of a specific text string in a column in excel

Question: How do I count the number of times a text string exists in a column? Answer: Array formula in […]Comments(9) Filed in category: Count text values, Count values, Excel

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

### Leave a Reply

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

<code>your formula</code>

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

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

VBA 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“ https://www.get-digital-help.com/2015/09/07/learn-the-basics-of-excel-arrays/ […]