Explaining Array Formulas in Excel

There is special class of formulas available in Excel know as Array formulas or CSE b’coz they are entered by hitting three keys at a time Ctrl+Shift+Enter. There is no doubt this is one of the most powerful feature provided by Microsoft Excel, but this is one of least used feature as well. Array formulas empower you to calculate that you can’t perform with standard Excel function.  

The "Help" in Excel defines them as below:

"An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments."

What motivated me to write on this post is also an interesting story. I have few very brilliant excel users as colleagues, one day one of them told me that he is not aware about the usage of array function. I tried to explain my bit, but he was not convinced with my elucidation.  That made me feels very strange! And decide to dedicate my next post to Array formulas.

I will not go into the details of this function and its application but will surely make you aware how it functions! So let visualize the data given below: 

and if I want you to know the sum of sales, provided more than 6 units are sold for Event product.

The function will go like this G13: = SUM((D2:D11="Event")*(E2:E11>6)*(G2:G11)) and hit Ctrl+Shift+Enter you will get the solution.

So array functions are helpful when we have more than one decisive factor for our result.

Important Tip! If your formula is not giving you the result you want and you believe you have done everything correctly, you can display the calculation in any part of your formula. To do so, just highlight the condition supplied and hit F9. 

It will give you result how Excel is seeing your condition in TRUE or FALSE.  It will give TRUE when the condition applies and FALSE where it fails.

This is a handy tip to remember for troubleshooting the parts of any formula that are giving you problems.

Before concluding this post I would like you to know the number one rule with array formula, use them only when needed and know when & where to use them. I am not trying to de-motivate you for using array formulas but too many arrays will slow down the functionality of your workbook.

And at last I would request to give your feedback for me and share posts with your contacts and social group.  Let’s continue enriching this mutual relation and help grow each other. “Happy Learning”