If you are a regular user of Excel, you most likely know about the array formula in Excel. Also, you could be aware of this, array formulas lessen our time and effort. In this article, we will explain how an array formula works in the memory of Excel. “In the memory of Excel” means how Excel processes an array formula. Here, we’ll discuss this topic with some Excel array formula examples. So, let’s start.

**Table of Contents**hide

## Introduction to Array in Excel

Before going into the details, let’s dwell a little on what an array in Excel is.

Generally, an array is a group of things. The elements may take the form of texts or numeric values, and they can place themselves in a single row, a single column, or numerous rows and columns. Let’s talk about the picture below.

From the image above, we can understand visually what an array is. Here, we have shown two types of arrays. One-dimensional and two-dimensional. We can differentiate one-dimensional arrays into two sub-types: horizontal and vertical.

## Basics of Array Formula

Prior to discussing Excel array formula examples, we’ll know about array formulas first. An array formula differs from a standard formula in that it handles numerous items as opposed to a single value. In other words, a formula for an array in Excel examines each value in the array individually and executes several computations on a single or a group of items in accordance with the conditions stated in the formula. There are two types of array formulas.

- First one that gives result in a single cell
- Another one, that gives the output as an array also.

We can utilize array formulas in Excel for analysis of data, sums with criteria and different types of lookups, linear algorithms, calculation of matrices, and a lot more.

## Array Formula Examples in Excel: 5 Ideal Examples

In this section, we’ll see some examples using the array formula in Excel. For ease of understanding, we’re utilizing a** Mark List of Students** of a certain institution. This dataset includes the **Names** of the students, and their corresponding marks in **Maths** and **Science** in columns **B**, **C**, and **D** respectively.

Now, we’ll use this dataset to show our first two examples. So let’s explore them one by one.

Here, we have used the *Microsoft Excel 365* version, you may use any other version according to your convenience. And if you face any inconvenience during usage, please let us know in the comment section.

### Example 01: Calculating Total Marks of Students

In our first example, we’ll calculate the total marks for each student using an array formula in Excel. It’s simple and easy. Just follow along.

**📌**** Steps:**

- At the very beginning, create a new column
**Total Marks**under**Column E**.

`=C5:C14+D5:D14`

- After that, press
**ENTER**.

*Note:** Check the image carefully until you see a light blue line around the cells in Column E. This is because they are in an array. We didn’t use the *

*Fill Handle**or anything like that to fill these cells*.

Here, we’re using *Microsoft Excel 365* version. So, we can run the array formula by pressing **ENTER**. But, in the older version, you must press **CTRL + SHIFT + ENTER** to use the array formula.

- So, go to cell
**E5**and enter the**same**formula. - After that, press
**CTRL + SHIFT + ENTER**at a time.

*Note:** After pressing these buttons, a pair of curly brackets will automatically be applied within two sides of the formula. You don’t have to write them manually*.

Also, you can’t make any changes inside the cell of an array. Excel will not allow you to do this. If you ever try to do this kind of thing, the following phenomena will happen.

- Here, go to cell
**E8**and change the cell element to**5**. - After that, press
**ENTER**.

Immediately, Excel will show a warning box on the display like the image below.

### Example 02: Determining Highest Marks Obtained

Now, we want to discover the highest marks obtained by the students on any of the two subjects. So, let’s begin.

**📌**** Steps:**

- Firstly, we’ve made an output range in
**B16:C16**. - Secondly, select cell
**C16**and write down the formula below.

`=MAX(C5:D14)`

Here, **the MAX function** returns the maximum value among the numbers in this range.

- Lastly, press
**ENTER**on the keyboard.

### Example 03: Excel Array Formula with Multiple Criteria

In this example, we’ll show you an array formula that will return a two-dimensional array with multiple criteria. Here, we’ve got the **Name**, **Section**, **Subject**, and **Mark** in columns **B**, **C**, **D**, and **E** consecutively. Also, we got **Section A** and **Maths** as the **Subject** in cells in the **B16:E17** range.

Now, we’ll filter out the above array in the **B5:E14** range with the criteria in the **B16:E17** range. We can clearly see there are two criteria. One is **Section**, another is **Subject**. So, obviously, there are multiple criteria for filtering. So, without further delay, let’s dive in!

**📌**** Steps:**

- At first, copy the headings in the
**B4:E4**range and paste them into the**B19:E19**range. - Then, go to cell
**B20**and paste the following formula into that cell.

`=FILTER(B5:E14,(C5:C14=B17)*(D5:D14=D17),"")`

Here, **B5:E14** represents the range of the array. **C5:C14** serves as the **Section** column. **B17** performs as the section to be filtered. **D5:D14** means the Subject column. Lastly, **D17** acts as the subject to be filtered.

The **FILTER function** has three arguments. Here, **B5:E14** is the ** array** argument. And

**(C5:C14=B17)*(D5:D14=D17)**is the

**argument. Basically, this works like a Boolean array; it carries the condition or criteria for filtering. In this case, we’ve two criteria combined by a**

*include***(*)**sign.

- After that, tap
**ENTER**.

### Example 04: Computing Total Cost based on Quantity

This example is actually fun to learn. You can apply this theory to other problems as well. Here, in **Column B**, we have the **Quantity of Unit**. And, in **Column C**, there is their corresponding **Unit Price**. In essence, we want to know how much this will cost if we order a certain quantity of units.

From the picture above, we can see that the unit price is decreasing gradually with the increase in order amount. So, it will cost us less if we order in bulk. Let’s see it in action.

**📌**** Steps:**

- Initially, go to cell
**C12**and put the following formula into the cell.

`=C11*IF(C11>=101,C9, IF(C11>=51, C8, IF(C11>=21, C7, IF( C11>=11, C6, IF(C11>=1, C5, "")))))`

**Formula Breakdown**

**IF(C11>=1, C5, ” “) →**The**IF**function checks whether a condition is met, and returns one value if**TRUE**, and another one if**FALSE**. Here,**C11>=1**is theargument which compares the value of the*logical_test***C11**cell with**1**. If this value is greater than or equal to**1**then the function returns the value of cell**C5**(argument) otherwise it returns blank(*value_if_true*argument).*value_if_false***Output → 20**

**IF( C11>=11, C6, IF(C11>=1, C5, “”)) →**this becomes**IF( C11>=11, C6, 20)**.**Output → 19**

**IF(C11>=21, C7, IF( C11>=11, C6, IF(C11>=1, C5, “”))) →**this becomes**IF(C11>=21, C7, 19)**.**Output → 16**

**IF(C11>=51, C8, IF(C11>=21, C7, IF( C11>=11, C6, IF(C11>=1, C5, “”)))) →**this becomes**IF(C11>=51, C8, 16)**.**Output → 15**

**IF(C11>=101,C9, IF(C11>=51, C8, IF(C11>=21, C7, IF( C11>=11, C6, IF(C11>=1, C5, “”))))) →**this becomes**IF(C11>=101,C9, 15)**.**Output → 15**

**C11*IF(C11>=101,C9, IF(C11>=51, C8, IF(C11>=21, C7, IF( C11>=11, C6, IF(C11>=1, C5, “”))))) →**this becomes**C11*15**.**Output → 80*15 → 1200**

- Following this, press the
**ENTER**key.

### Example 05: Finding out Average of Positive Numbers

In this worksheet, you see there are some numbers in **Column B** of the worksheet. Some numbers are positive, and some numbers are negative. We are going to find out the average of the positive numbers in this range.

You can do it in two ways. The first way is: separate the positive numbers manually, create a new range, and at the end use **the AVERAGE function** to find out the average of the positive numbers. Another way is: we can use an array formula. It will save us time and effort. So, without further delay, let’s see how we can do it.

**📌**** Steps:**

- Primarily, go to cell
**C5**and enter the following formula.

`=AVERAGE(IF(B5:B14>0,B5:B14,FALSE))`

**Formula Breakdown**

Observe closely the arguments of the **IF function**. You know how the **IF function** works. If the first argument of the function is **TRUE**, then the second argument is returned by the **IF function**. If the first argument is **FALSE**, then the third argument is returned by the **IF function**. Arguments are separated by commas.

In our case, the third argument is **FALSE**, so if the first argument is **FALSE**, then the **IF function** will return the **FALSE** statement.

OKAY! The main focus is now on the first argument and the second argument. You see the first argument is a range, and the second argument is also a range. And the whole formula is an array formula.

**IF(B5:B14>0,B5:B14,FALSE) →**here Excel will create an array internally with the positive numbers and**False**statements.**Output → {15, FALSE, 14, FALSE, FALSE, 45, 78, 65, 54, FALSE}**

**AVERAGE(IF(B5:B14>0,B5:B14,FALSE)) →**this becomes**AVERAGE({15, FALSE, 14, FALSE, FALSE, 45, 78, 65, 54, FALSE})**.**Output → 45.167**

The **AVERAGE function** finds out the average of the values in the array, except the **FALSE** values, the **False** values are not numbers, so the **AVERAGE function** neglects the **FALSE** values.

- As usual, press
**ENTER**.

So this is how an array formula works. You see the formula starts with the opening curly bracket and closes with the ending curly bracket, so this is undoubtedly an array formula. You know this curly bracket is applied on an array formula automatically by Excel when you press **CTRL**, **SHIFT,** and **ENTER** keys simultaneously on your keyboard.

## What to Do If Excel Array Formula Is Not Working

This article is based on the *Excel 365* version. Therefore, if you are using an older version of Excel than the *Excel 2019* version, then you must press **CTRL + SHIFT + ENTER** to use the array formula. If you just press **ENTER**, then your array formula will not work except for some functions such as the **AGGREGATE** and **SUMPRODUCT** functions.

## Things to Remember

- You cannot construct an array with an entire column or multiple columns. Because this is very greedy oligarchs. It can cause malfunctioning of the systems.
- You can use the
**F9**key to debug any part of an array formula. It will make it easy to evaluate them. - You need not place the curly bracket yourself. Excel will do it automatically.
- Remember that you cannot make any change to a cell in an array. You have two choices. Either edit the formula or delete the formula and recreate it in your preferred way.

## Practice Section

Though it is easy, it needs practice. That’s why we have attached practice sheets for you so that you can internalize the methods that you find easy and useful. Please do it by yourself. If you find any difficulty while doing practice, please inform us.

You may download the following Excel workbook for better understanding and practice yourself.

## Conclusion

So this is how an array formula works. In this article, I have just introduced you to array formulas. Its scope is much wider. In this article, we have just shown some of the excel array formula examples. Don’t forget to download the **Practice** file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

Big-time Thank You!

I am finally starting to “get it”! So many texts and web helper sites seem to be speaking either to infants or to PHDs. Your tone and style is simple without being inane. Interesting without being unnecessarily complicated. Great job!

Many thanks, Scott!

This is in awesome website and you are much more awesome Kawser!!!. I m loving excel now and will refer this to my friends too!!!. I will request you to plz share a practice worksheet containing advance excel problems for self practice. You should share the solutions on periodic basis. Thanks

It’s a good idea, Rahul. I will think about it soon.

Best regards

Kawser

Really straight forward and easy to understand… I am off to search for more of these array posts from you. Excited to see which is next!

Hello Jay, thanks for your nice words. Please visit our blog for more array posts! We have written more posts like this time by time.

Thank you very much for this tutorial, very useful in understanding what an array is and how it works in Excel.

You are welcome, Surya! 🙂

Excellent

Thank you so much, Ferreira. Visit our blog and explore more!

Hi

Got your “Excel Array Formula Basic 2 – Breakdown of Array Formula” great.

Question what is the name or address of Basin1

This would help me with Array Formula.

Bill

Hi Bill Coriell! Sorry for being late in reply. Maybe you were asking for the following article.

https://www.exceldemy.com/what-is-an-array-in-excel/

With regards

-ExelDemy team