Array formulas are the most powerful formulas in the Excel. But one should know when to apply or not apply these array formulas

1.What is the necessity of  Array Formlas?

Say, you have data in range A2:A10. Now you want to add all the values in this range what will you do? simply press =SUM(A2:A10)

Suppose you want to multiply each cell value in the range A2:A10 with 2 before summing up what should you do? or Say you want to multiply Range A2:A10 with range B2:B10 before summing up what shall you do? To do this you have two options:

Option 1: Insert one more column C and multiply Column A with Column B and use Sum formula in column C to get the result.

Option2: Use the array formula  like this {=sum(A2:A10*2)} or {=sum(A2:A10*B2:B10)}

In simple words, Array formulas gives direct results without intermediate steps. That’s why they are most powerful formulas.

2.What is the word “Array” means in Array Formula?
It simply means a range or series of data

3. How to enter these Array formulas in Excel?
After typing the formula instead of usual “Enter” press Ctrl+Shift+Enter. Then you should notice { } braces surrounding the formula. You can’t enter these braces into formula manually you must press Ctrl+Shift+Enter

4. What are the differenet types of Array Formulas ?

Array formulas can be broadly classified into two types based on the end result of the formula VIZ.

a. Single Cell - Array Formula - if end result returns a single value to the single cell

b. Multi Cell - Array Formula - if the end result also a range or array returning values to multiple cells

In the above picture, what you see is a single cell array formula.

5.How to create multi cell array formulas?

For instance see this multi cell picture. In this case we have a matrix of 2×4, we have converted this into 4×2 matrix by entering Transpose function as the multi cell array formula.

6.How to enter multi cell array formulas?

For entering multi cell array formulas first you should know the size of the resultant area / matrix. In the above picture the size of the resultant matrix is 4×2 so we have to first select the range from B6:C9 and enter the formula in single cell and press Ctrl+Shift+Enter. Formula will automatically spreads across the all selected cells. If you can notice all these cells contains exactly the same formula.

7.When we will use these Muti cell array formulas?

In business scenarios to predict the trends by giving set of inputs and get the outputs as array. You can try the Trend Function to see this.

8.Is there any down side for these array formulas?

Yes. Array formulas are resource hungry. They occupy lot of memory compare to other normal formulas. Sometimes they slow down the calculation process of spread sheets. So use them wisely. But compared to user defined functions VBA these array formulas are fast.

9.Is there any formula in Excel which has power of array formulas and speed of normal formulas?

Yes. The one and only formula and The Formula in entire Excel is SUMPRODUCT formula.

10. When we have this Sumproduct formula, why one should go for Array formulas?

In more than 90% cases, what array formulas can do Sumproduct formula can do. I personally use array formulas only when i cant do something with Sumproduct formula.

Tweet This