These are a much overlooked feature of Excel, largely because array functions can be hard to understand. The power of many regular formulas such as countif, sumif that you use every day can be increased significantly by applying them to arrays.
What are they ?
Actually the name is rather explanatory. You take an array, or list, of things and do an operation on each of them individually, producing an intermediate list of results, which may then be summarized in some way. In order to indicate that something is to be treated as an array formula, after you finish entering it in the formula bar, hit control/shift/enter instead of enter (this is why they are sometimes called CSE formulas). If you do it right, {} will appear round your formula. Forgetting to do this is biggest single annoyance when working with array formulas. The upside of using arrays formulas is that you will be able to very complex and cool things in one formula, where you would otherwise need to resort to VBA. The downside is that they can be complicated to get used and to debug if you don’t use them regularly, and they can slow down your worksheet recalculation so that it becomes unworkable. My advice is to use them with caution, especially if others less experienced might need to maintain or understand your spreadsheet.
Debugging
The first thing you better learn if you are going to work with array formulas is how to debug them. You can use Formulas/Evaluate Formula in Excel to look at the intermediate contents of each array of values in the calculation. I prefer to use a function that displays them in the cell – we’ll see how to use that in a minute, and later how we can use it to intercept array formula results. If you are comfortable with VBA, here is a function to print out the contents of an array Function afConcat(arr() As Variant) As String Dim i As Long, s As String s = “” For i = LBound(arr) To UBound(arr) s = s & arr(i, 1) & “|” Next i afConcat = sEnd Function
Array constants
Lets get started with something straighforward. You can set up an array of constants. In this case, and i think this is the only case, you need to enter the {} manually. We’re going to put this array of constants in a named range. Create a named range, called afColumn with a value of ={“Eggs”;”Bacon”;”Sausages”}.
If you have created the VB procedure afConcat mentioned earlier, in a spare cell enter
=afConcat (afColumn)
this should evaluate to Eggs|Bacon|Sausages|
Now select 3 cells, A1:A3, and in the formula bar, type =afColumn, then complete the action with control/shift/Enter. It should go like this, and fill them in automatically.
Try it again with cells a5:a6 – this time you get only eggs & bacon. This is pretty cool, and gives you an easy way to fill up ranges or parts of ranges with list of things. ={“Eggs”;”Bacon”;”Sausages”} works for a vertical range, but you can do a horizontal range as well.
This time create a named range called afRow with a value of ={“Eggs”,”Bacon”,”Sausages”}. Using comma instead of semicolon to separate the array elements indicates that this is a horizontal rather than vertical array. This is what you get when you select B1:d1, and enter =afrow finished off with control/shift/Enter
Mixing commas & semicolons allows you to create a block of data that can be referenced wherever you want to create headings, lists, tables as required.
Note that the afConcat function only works for columns – but you can still do something to use it for rows as we’ll see in a moment.
Array formulas returning lists of values
All that is very interesting, pretty cool, but of limited use. In the next section we are going to look at the power of array formulas where the lists returned by arrays are summarized into a single value result.
Before we get there though, try this one.
Select cells E1:E3 and enter =transpose(B1:D1) as an array formula – Something pretty cool happens, as below. Rows become columns, columns become rows. This is something that you often need to do – reorganize columns into headings.
Finally, you can use transpose to have afConcat display horizontal arrays using transpose- try this entered as an array with control/shift/enter
=afConcat( TRANSPOSE (aFRow))