Excel Array Formula Basic – What is an Array in Excel?

Today, I will talk about the basics of Excel Array Formulas. Most Excel users find it one of the hardest topics to understand in Excel. There are several articles online already on this topic.

Then why am I writing this one again?

I found most of those articles are complex for beginners. I will write a series of articles on Array Formula (this one is the first one) and I will make understanding Excel Array Formulas as easy as possible.

Those who want to become a master in Excel formulas must have to know how to handle arrays in formulas. Before understanding array formulas, you have to know what Excel Arrays are. If you know any programming language, then you know about array very well. If you have no idea about a programming language, then don’t worry. It is very simple.

Excel Array Formula Examples

An Array is just a list of some items. The items might be some values like this list (green colored in the following image), or some text like this list (red colored in the following image), or mixed up with both values and text like this list (blue colored in the following image).

Excel Array Formulas Image 1

Figure 1 [Arrays in different forms]

There is something common between these three lists. All of the items are listed within curly braces. And another thing is when the items are text; they are listed within double quotation marks. The first array has total 8 items. The second array has total 4 items and the last array has total 5 items.

On the right side of the worksheet (above image), there are same arrays but the items are separated by semi-colons. The difference between commas and semicolons is that comma separated arrays are entered into the worksheet horizontally, and semi-colon separated arrays are entered into the worksheet vertically. An array can also have both comma and semi-colon and that is called multi-dimensional array. In this article, I shall not cover the multi-dimensional array concept. It will be covered in details later.

How to Enter Array Formula in Excel Horizontally

Let’s see now how you can input an array into a cell or into more than one cell. The first array has 8 values, so you need to select 8 cells before entering this array into the worksheet.

Select total 8 cells horizontally (from cell C9 to J9) as the items are comma separated. Input an equal sign, opening curly brace, then type 8, comma, 10, comma, 2, comma, 5, comma, 7, comma, 15, comma, 9, comma, and 1, comma. And close the curly brace.

Excel Array formulas Image 2

Entering an array into worksheet cells horizontally!

To enter a regular formula into a cell, we press “Enter” key on the keyboard. But to enter an array formula into a cell, you have to press CTRL + SHIFT + Enter Key on the keyboard. This is one of the basic differences between a regular formula and an array formula. Remember it very well. Press CTRL SHIFT and ENTER key in the keyboard. You will find that the array values are set in the eight selected cells (horizontally).

Let’s now see what happens when we enter the same array in less than eight cells. I select five horizontal cells this time (from cell C11 to G11), type the same array formula, press CTRL SHIFT and ENTER key in the keyboard, you see the first 5 values of the Array are entered into the 5 cells. The rest 3 values are not entered.

Excel Array Formulas Image 1

If selected cells are lower than the number of values in an array, only those first values equal to the number of selected cells value will be shown.

Let’s now check what happens when we select more than 8 cells to enter this array. I select 10 cells horizontally (from cell C13 to L13), type the same array, and press CTRL SHIFT and ENTER key on the keyboard. You see the first 8 cells are filled with the 8 values, the rest 2 cells are showing errors.

Excel Array Formulas Image 4

When the number of selected cells is higher than the number of values in the array, there will be some errors (#N/A)

Let’s now enter this array vertically, I select eight cells vertically (from cell N9 to N16), type the same array, press CTRL SHIFT and ENTER key in the keyboard. You see only the first value, 8, has been entered into all the cells.

Excel Array Formulas Image 5

Horizontal array entered into vertical worksheet cells.

Now I select a range that has 8 cells horizontally, and 5 cells vertically (from cell C15 to J19), type the array values, and press CTRL SHIFT and Enter key in the keyboard. You see the range is filled with the repeated array. So this is the basic way to enter an array into a cell.

Analysis of an array formula

Take a look at the formula bar, you see a new curly brace is added before the equal sign after we have pressed CTRL SHIFT and ENTER keys. We have entered only the equal sign and the curly bracket with the values, after pressing CTRL SHIFT and ENTER keys in the keyboard, a new curly brace is enclosing all the contents in the formula bar. When you will get this type of formula structure, you will treat it as an Array formula. This is the sign to differentiate the array formula from a regular Excel formula.

Excel Array Formula Image 6

Excel Array Formula

How to Enter Array Formula in Excel vertically

Let’s now enter a vertical array into the worksheet. The array (in orange color) has total 5 items and the items are semicolon separated. Select total 5 cells vertically (from cell P9 to P13); input equal sign, then input opening curly brace, type 5 semicolon, Apple in double quotation mark, semi-colon, 8, semi-colon, Orange in double quotation mark, semicolon and finally Banana in double quotation mark. Close the Curly Brace. Press CTRL SHIFT and ENTER keys in the keyboard. And you see the items are set into the cells vertically.

Excel Array Formulas Image 7

Array formula entered vertically

Note

Remember one thing; you can change the array formula in a single cell. Let’s say you want to change the formula of this Orange cell. Select the cell and edit the formula in this way: From Banana to Mango. Press CTRL SHIFT and ENTER key simultaneously in the keyboard. The Array formula is changed for all the cells.

Excel Array Formulas Image 8

Editing array formula

If you press only Enter after the change in the array formula, an error message will appear. I change again the array formula from Mango to Banana, press only Enter key this time; you see a dialog box appears with a message that you cannot change a part of the array. Click OK. The dialog box disappears. Whatever cell you select within an array formula; the formula is same for all the cells in the formula bar.

Excel Array Formulas Image 9

You cannot change part of an array formula.

So this is how you can enter an array into a cell or a range as the array formula.

Download working file

Download the working files from the link below.

Introducing_Array_Formulas.xlsx

Quick Summary

⇒ Example of Arrays in Excel:

  1. Horizontal Array (separated by comma): {8, 10, 2, 5, 7, 15, 9, 1}
  2. Horizontal Array (separated by comma): {“Apple”, “Orange”, “Banana”, “Mango”}
  3. Vertical Array (separated by semicolon): {8; 10; 2; 5; 7; 15; 9; 1}
  4. Vertical Array (separated by semicolon): {“Apple”; “Orange”; “Banana”; “Mango”}

⇒ To enter an array formula, you have to use CTRL + SHIFT + ENTER keys simultaneously. I say this keyword combination as CSE.

⇒ For example, an Excel array has 8 elements.

  1. You have selected 8 cells to enter that array. Then all the elements will be entered into the cells.
  2. You have selected less than 8 cells (say it is 5) to enter that array. Then 5 cells will take the first 5 elements of the array.
  3. You have selected more than 8 cells (say it is 12) to enter that array. Then all the 8 elements of that array will be entered into the first 8 cells, the rest 4 cells will show #N/A error.

⇒ To Edit an array formula: Just select any cell that holds the array  edit the array  and press CTRL + SHIFT + ENTER keys simultaneously. You are done. All the arrays will be edited.

Read More:


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

We will be happy to hear your thoughts

      Leave a reply