How to use Excel Array Functions

There is a secret realm of functions in Excel known as array functions. These functions can do the work of many functions and are extremely powerful. If you are reading about array functions for the first time it can take a while to full understand them. But once you do you have unlocked the door to an extremely useful skill only truly understood by about 5% of Excel users.

The main rule when using Excel array functions is to press Ctrl, Shift and Enter when entering them. If you press Enter like when entering regular Excel functions you will see the rather ambiguous #VALUE error. Not very helpful, but this error is letting you know that Ctrl, Shift and Enter has to be used every time you enter or edit an array function.

After pressing Ctrl, Shift and Enter curly braces { } enclose the formula. These curly braces are not entered manually and only appear after pressing the Ctrl, Shift and Enter keys. Because of this unique action, array functions are also known as CSE formulas. This name serves as a reminder to the use of Ctrl, Shift and Enter keys and also to hide the confusing and scary term of array for first time users.

Example 1

A single array function can replace hundreds of intermediate formulas. The example below shows the multiplication and addition of cells all performed within one formula. The intermediate formulas of C2*D2, C3*D3 etc have been avoided.

=sum(C2:C11*D2:D11)

Single cell excel array function

Example 2

Array functions are also ideal for summing and counting cells based on multiple criteria. This example looks at adding all the order amounts where the month was May and the category was Sweets.

=SUM((A2:A11=”May”)*(B2:B11=”Sweets”)*(F2:F11))

array function testing multiple conditions

Why use Array Functions in Excel

Array functions are extremely powerful but are not always necessary. Their achievements can sometimes be accomplished using regular functions such as the database functions, and from Excel 2007 the likes of AVERAGEIF and SUMIFS.

Some advantages to using Excel array functions include:

  • Smaller file sizes due to the reduction in formulas used.
  • Consistency with formulas. In example 1 above, there is not multiple cells with different formulas such as C2*D2 and C3*D3. These have been included in one cell.

Disadvantages to using array functions include:

  • Using too many in your workbook will slowdown calculations and actions such as saving and opening Alternative such as the database functions and pivot tables are ideal for analysing tables of data and are much faster at calculating than array functions.
  • Your colleagues may not have an understanding of array functions making using them counterproductive.
  • You must press Ctrl, Shift and Enter each time you enter or edit the formula. This is easy to forget as it is ‘against the grain’ and you start seeing errors.

Finally

When you understand Excel array functions you can consider yourself a master of Excel formulas. It is a skill coveted by only a small percentage of Excel users. So you are part of a special group.

Array functions can do things in Excel that regular functions are just not capable of. However you need to be careful not to overuse them or your computer will slow down. Avoiding these pitfalls is how you can be successful with their use.

Advertisements