Array in Excel Uses, Benefits and Details with examples in array functions formula and constants

Hi friends,

While discussing about functions in Excel, I used the term ARRAY several times in this blog and in my YouTube Videos and briefly I described array as set of numbers or strings.

Click to Watch YouTube Video about Array

In today's article I'll discuss about array in details which is very important topic in Excel, but before going to the discussion directly few questions may arise in your mind and these could be

What is array ? Why should I learn about Array in Microsoft Excel ? And most important, what is the utility of array ? Where to use array ? How to use array ?

Friends, I'll discuss all these questions in details today with examples using various functions.

The first question I am going to answer is, "Why should I learn about Array in Microsoft Excel ?"...

So, let us understand it by the help of an example.

If few name is given as "ZZZ","CCC","QQQ","DDD" and the sales value as 50,37,22,51 and I ask you to find out the two largest value and their total value from these numbers then most probably you will give me 51+50 =101.

And if I ask you how did you find it then probably you will say you used the function MAX() or LARGE() or you manually searched the data to find out the two largest number and then used SUM() function.

But if I give you ten thousand (10000) numbers and then ask you to add 100 largest numbers then it is a huge problem because now you can't do it by the above mentioned processes, you need some simplified formula or technique to get the result quickly.

In this situation ARRAY will help you the most, now I'll discuss about ARRAY step-by-step with examples with some functions.

Interested ? Continue reading...

Array in Excel an extremely powerful tool and one of the most difficult to master. But I'll make it simple so that you can use it easily. 

A single Excel array formula can perform multiple calculations and replace thousands of usual formulas. And still, 90% of Excel users have never used array functions in their worksheets simply because they are scared to start learning them. So, don't fear, lets understand it first.

What is an array in Excel ?

An array in Excel is a collection of items. These items can either be text or numbers or both or some special character and they can locate in a single row or column, or in multiple rows and columns.

Depending upon their nature we specify them as single dimensional two dimensional and multi dimensional etc.

Example of array, as I mentioned earlier "ZZZ","CCC","QQQ","DDD" could be an array or 1, 2, 5, 62, 77, 24,100 etc might be the array. So in simpler language we can say array is a set of items.

ARRAY in Excel

Now, how to declare array in Excel ?

Suppose you have "ZZZ","CCC","QQQ","DDD" in cells B2 to B5 in excel as shown in picture, select any cell and in formula bar write '=' and select the range from B2 to B5 and press Ctrl+Shift+Enter (Control + Shift + Enter) key, you will see automatically there are two curly braces are please before '=' sign and at the end of the cell range in formula bar like "{=B2:B5}".

ARRAY in Excel with curly braces

Wow... You just created an array...!!! A single dimensional array. Is it not simple ?

Yes, it is. Now what is the difference between Array Formula and regular Excel Formula ?

The difference between an array formulas and regular Excel formulas is that an array formula processes several values instead of just one. In other words, an array formula in Excel evaluates all individual values in an array and performs multiple calculations on one or several items according to the conditions expressed in the formula.

OK I am making it simple. In the first example using MAX() or LARGE() function you can derive only one result but if you use array it will help you to derive more than one value and you can easily use your main formula to calculate it.

Let me explain it with LARGE() function. The syntax of LARGE() is

=LARGE(array, k) where array is the items or in excel cell range and 'k' is the position to return.

For Example :  given 1, 2, 5, 62, 77, 24,100 if you use LARGE() function it will look like

=LARGE(A2:A7, 2)

Here A2:A7 is the cell range, you know it, but what '2' signifies ? It instructs LARGE() function to diaply the second largest number in the array provided. So, the result would be 77. If you wite '1' replacing '2' it will give 100.

Now can you derive largest 100 numbers out of 10000 numbers ? 

No, without using array it is difficult because you are allowed to enter only one data or position in the LARGE(array, k) function.

How to enter array formula in Excel (Ctrl + Shift + Enter) ?

As you already know, the combination of the 3 keys CTRL + SHIFT + ENTER turns a regular formula into an array formula.

Few Important Things To Keep In Mind:

1. Once you've finished typing the formula and simultaneously pressed the keys CTRL + SHIFT + ENTER, Excel automatically encloses the formula between {curly braces}. When you select such a cell(s), you can see the braces in the formula bar, which gives you a clue that an array formula is in there.

2. Manually typing the braces around a formula won't convert it into an array formula. You must press the Ctrl+Shift+Enter shortcut to complete an array formula.

3. Every time you edit an array formula, the braces disappear and you must press Ctrl+Shift+Enter again to save the changes in your formula.

4. If you forget to press Ctrl+Shift+Enter, your formula will behave like a usual Excel formula and process only the first value(s) in the specified array(s). Therefore, always check whether the array exists or not.

Press 'F9' key in formula bar to evaluate portions of an array formula

While working with array formulas in Excel, you can observe how they calculate and store their items (internal arrays) to display the final result you see in a cell. To do this, select one or several arguments within a function's parentheses (first bracket), and then press the F9 key. To exit the formula evaluation mode, press the Esc key.

So lets' use array.

Suppose in the given Excel Table I need to derive the data which is maximum when we deduct Sales from Target, meaning need the gap of Sales & Target. So what we will do is we'll first type the function MAX(), this function's arguments are number 1, number 2 etc., so write =MAX(Select the Target Range, put a minus sign, Select the Sales Range). After putting the closing bracket of MAX function press Ctrl+Shift+Enter simultaneously. Instantly you'll see that this function is enclosed by two curly braces {}, the formula now converted to array function {=MAX(D2:D14-E2:E14)} and the result is 204, as shown in the image. If you don't press Ctrl+Shift+Enter, the result will be 50.

How to use MAX function with ARRAY

Example 1. A single-cell array formula

Already discussed about it. An Excel array formula does not need an additional column since it perfectly stores intermediate results in memory. So, you just enter the following formula and press Ctrl + Shift + Enter as shown above.

=MAX(D2:D14-E2:E14

Example 2. Using an Excel array function to return a multi-cell array

Excel array formulas can return a result in a single cell or in multiple cells. An array formula entered in a range of cells is called a multi-cell formula. An array formula residing in a single cell is called a single-cell formula.

There exist a few Excel array functions that are designed to return multi-cell arrays, for example TRANSPOSE, TREND, FREQUENCY, etc.

Other functions, such as SUM, AVERAGE, AGGREGATE, MAX, MIN, can calculate array expressions when entered into a single cell by using Ctrl + Shift + Enter.

As already mentioned, Microsoft Excel provides few "array functions" that are specially designed to work with multi-cell arrays. Excel TRANSPOSE is one of such functions and we are going to utilize it to transpose the above table, i.e. convert rows to columns. (Already discussed in my previous blog post)

Select an empty range of cells where you want to output the transposed table. Since we are converting rows to columns, be sure to select the same number of rows and columns as your source table has columns and rows, respectively. 

Enter the array function =TRANSPOSE(array) and press Ctrl + Shift + Enter.

This will reverse the arrangement as shown in the below image.

TRANSPOSE and ARRAY

Example 2 : Another Example in SUM() and LARGE()

In the below example I need the sum of top three numbers in Sales (E) column. The steps are type =sum(large(Select the range,now you can manually enter the curly braces and write 1, 2, 3 it will work or alternatively you can use Transpose() and press F9 in the formula bar and then write the LARGE() and SUM() functions respectively.

To visualize the working of ARRAY, please visit my YouTube Channel and view the array function.

How to use SUM and LARGE function in Microsoft Excel Array

So, friends like these examples you can frame any formula you wish, ARRAY is such a tool provided by Microsoft Excel that can finish your job which might have taken hours. Please visit my YouTube Channel and view those are my Blog's supportive video. 

Click to Watch YouTube Video about Array

Thank you...