Functions: Working with Arrays

Hi there. This is last post for this year. Let's say bye bye to year 2013 and welcome 2014 with heart full of joy.

As we started working with functions, we now know and has experienced that Excel allows us to perform fairly complex calculations with functions. You have to nest functions within functions to make it a mega function or do the calculations in multiple steps.

Assume a situation where in I have number of cells containing various text strings. I need to find out how many characters are present in all cells put together. What do I do normally is, insert a column next to text strings, enter a formula to calculate length of each cell (I know that Excel provides a formula i.e. LEN to calculate number of characters in a cell including spaces). Copy the formula all the way to bottom cell and enter another SUM formula one cell below the bottom cell to add up all the LEN results. This gives me what I wanted. Pretty straight forward, isn't it?

Right, what if I don't have the flexibility of inserting a row? What if I want to be less clumsy? What if I wanted a faster solution? Answers to these questions introduce us to another useful feature of Excel which is Arrays.

Though standard way of using functions works for many novice users, advanced users feel that working with Arrays are faster and efficient in many ways. Though there are drawbacks to Array functions they are lesser than the benefits. Let's go in to details.

Introduction

Simply put, an Array is a collection of cells. Collection of row cells is called one dimensional horizontal array and column cells collection is one dimensional vertical array. If you have multiple rows and columns, it is called a two dimensional array.

Array formula is a formula that works with arrays. There are multi cell array formulas and array formulas that calculate result in a single cell. Array formula works just like standard formula we enter in Excel. Only difference is that you have enter Ctrl+Shift+Enter whenever you are done with entering formula instead of just enter. So Array formulas are also called as CSE (Ctrl+Shift+Enter) formulas.

Let's take the above LEN example and see how Array formula will make life easier. I have a worksheet that looks like this.

What do I normally do is something like this. (Column B is just to show the formulas)

If I don't want extra column or simply cannot use one more column or doesn't have enough time for all this, I will just use an Array formula. I will select any available cell and enter the below formula.

=SUM(LEN(A1:A14))

Note that I have to enter Ctrl+Shift+Enter after I type the formula. Once done, Excel will show formula like this.

{=SUM(LEN(A1:A14))}

Note the braces entered by Excel. You should not put these braces manually. Result 59 is displayed in a single cell in a fraction of seconds, all that without any additional steps. Simple? Yeah. Thumbs up for Array formula.

Above is an example for single cell array formula. Result is spread across only one cell. Let's see an array formula which produces results that spread across cells. 

Take the same LEN example. Here is a quick recap on what we have. I have cells A1 to A14 filled with text and I need to find out number of characters in each cell and then total of characters across cells. Let's do the below steps.

1. Select cells B1 to B14
2. Enter the formula =LEN(A1:A14)
3. Press Ctrl+Shift+Enter
4. You should be able to see braces entered by Excel and result filled across B1 to B14
5. Enter formula =SUM(B1:B14) in B15 and enter
6. You should see result 59

So without entering or dragging LEN formula across the cells, we are finishing our work with single formula. Step 1 introduces an important concept in Arrays, that is, for multi cell formula, you have to select result cells in advance.

Did you notice the difference in standard usage of function and with Array. Standard function will have a different LEN formula for each cell. Array formula will have same LEN function for each of the cell. Look above picture carefully if you don't believe.

So since we have an understanding of how an array formula works, let's also see about major advantages and disadvantages of this.

Advantages:

1. Speed: We saved at least couple of steps in simple example above. Consider having thousands of rows of data and complex operations.
2. Flexibility: Array formulas offer greater flexibility towards nesting and performing complex calculations.
3. Consistency: Array formula is same across cells. This also ensures greater accuracy.
4. Safety: You cannot overwrite part of multi cell formula. In above example, try changing formula in one cell without selecting other array formula affected cells. Excel throws an error that you simply cannot do it.
5. You already know that multiple calculations makes Excel work slower and increases the file size. Array formula reduces this impact because we will be using fewer formulas.

Disadvantages:

1. You should always finish entering functions with Ctrl+Shift+Enter. If you forget this, your formula becomes normal formula and often Excel complains.
2. Array formulas are not so well known. If you send your file to other users, chances are there that they will be confused.
3. Large array formulas will slow down the system.

Points to remember:

• Always press Ctrl+Shift+Enter with Array functions
• Select all the cells where your result will spread before entering the formula.
• You can't change formula for partial cells in a multi cell array
• You can't insert new rows or columns or cells in between a multi cell array
• You can expand your array formula to include new cells but cannot shrink it. Only way to shrink is to delete existing formula and create a new one with fewer cells selected.

Though there is lot to know about Arrays practically, I will conclude it here for want of space and time. Post a comment to let me know if you have any particular doubts on this subject or want me to cover any specific area in future. 

Happy New Year!! May this new year gives you what ever you wish in life and much more. See you next year!! Bye.