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 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 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.
=C11*IF(C11>=101,C9, IF(C11>=51, C8, IF(C11>=21, C7, IF( C11>=11, C6, IF(C11>=1, C5, "")))))
- 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 the logical_test argument which compares the value of the C11 cell with 1. If this value is greater than or equal to 1 then the function returns the value of cell C5 (value_if_true argument) otherwise it returns blank(value_if_false argument).
- 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))
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