Array functions were one of my first forays into Excel. But I couldn’t do enough justice to them in the beginning, and hence felt the need for another post.

*What led to this post? *I was looking for a bit on Arrays, when I bumped into sites I hadn’t referred to before. And believe me, there is more to Arrays than even what I know now! But I thought some parts of it I must bring out. It all started when I was looking to get the list of unique items, and that is when I bumped into the formula I described in my prev. post. The Power Formula one. It was quite heavy in Arrays, so I decided to probe a little further into how it works, and then the ball rolled over from one topic to another, and finally landed here – 3D arrays in Excel!

**Arrays in Excel Sheet space**

If you want to enter an array, all you have to do is enter this: ={0,1,2,3,4,5}. And if you “Array-enter”(Ctrl+Shift+Enter) it, you find the 6 cells (in columns) populated but the array. So basically, this is it to enter an array.

**Named Array Ranges** : This is a little different from the usual named ranges we have in Excel. Usually, a name defines/refers to a specific cell contents. So it’s actually a reference to the cell location, and **not a formula**! If we need to name the array we entered above, and we do the usual naming on the top left corner cell, I think you’re going to be in for some surprise when you use the name.

Essentially, named array ranges are the same as *“Array Variables”*. So, when you go to the Name Manager, you need to specify the name as shown in the screenshot. This way, when you use the name, it substitutes the Array in its place.

This is such a handy option, and if you get the hang of Arrays, I think you’d be in love it it. If you’re used to matrices processing or stuff, like Matlab, I think you’ll find this good.

To know more on what all an Array in Excel can support, look up this link (http://www.emailoffice.com/excel/arrays-bobumlas.html) which gives every possible way of ‘how to use arrays’. The Calendar in there is too damned good a formula, that I trying to make a sweet calendar for a 25 year period with features like what Outlook offers, w/o the Mail options.

Setting out on this calendar stuff is when I hit upon the topic of 3D Arrays.

**3D Arrays in Excel**: In Excel sheet space, 3D array refers to cell ranges across multiple sheets, for eg. ({=Sheet1:3!A$1:A$3}) is considered a 3D array formula, since it involves 3 Dimension – Rows, Columns & Worksheets. The following link has some information on this (http://www.bettersolutions.com/excel/EED883/QN620422111.htm). But nowhere was I able to find a 3D array similar to the lines of using it in VBA i.e. X(1:2:3). In VBA, just a Dim X(2,2,2) as Variant makes it an array to hold a 3x3x3 matrix. I haven’t found enough documentation whether this is possible in Excel Sheet space.

Coming back the the power formula, it sure does suit its name, for there is so much* “array munching n crunching”* that happens in the formulas, it has definitely expanded my comfort zone as far as Arrays are concerned.