5 Examples of Using Array Formula in Excel

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.

Introduction to Array in Excel

Before going into the details, let’s dwell a little on what is an array in Excel.
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.

• Then, select cell E5 and enter the following formula.

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

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.

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 include 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 (*) 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.

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

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

Download Practice Workbook

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.

Related Articles

• How to Create Array Formula in Excel
• How to Copy Array Formula in Excel

<< Go Back to Array Formula | Excel Formulas | Learn Excel