Array Formulas in Excel – 3D Arrays

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.Named Array Range

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 ( 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 ( 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.


21 thoughts on “Array Formulas in Excel – 3D Arrays

  1. Rudy Zimmer

    I have been surfing on-line greater than 3 hours lately, but I by no means discovered any fascinating article like yours. It’s beautiful price sufficient for me. In my view, if all webmasters and bloggers made good content material as you probably did, the net shall be much more useful than ever before. “When there is a lack of honor in government, the morals of the whole people are poisoned.” by Herbert Clark Hoover.

  2. Jeni Steffey

    Excellent post. I was checking continuously this blog and I am impressed! Extremely useful info particularly the last part 🙂 I care for such info a lot. I was looking for this particular information for a long time. Thank you and best of luck.

  3. Corinne Jandreau

    Hello there! This post couldn’t be prepared any superior! Reading by means of this post reminds me of my earlier place mate! He generally kept talking about this. I’ll ahead this informative article to him. Fairly confident he will have a fine learn. Thanks for sharing!

  4. Gaston Arriola

    Thanks for the marvelous posting! I actually enjoyed reading it, you are a great author.I will make certain to bookmark your blog and will come back from now on. I want to encourage you to continue your great work, have a nice afternoon!

  5. Jacklyn Eutsler

    I would like to thnkx for the efforts you’ve put in writing this site. I am hoping the same high-grade blog post from you in the upcoming also. In fact your creative writing abilities has inspired me to get my own website now. Actually the blogging is spreading its wings quickly. Your write up is a great example of it.

  6. Johnathon Arabia

    Appreciating the commitment you put into your site and detailed information you offer. It’s nice to come across a blog every once in a while that isn’t the same unwanted rehashed material. Fantastic read! I’ve saved your site and I’m adding your RSS feeds to my Google account.

  7. Jewelry Deal of the Day

    lol a couple of the reviews bloggers write are really silly and unrelated, there are times i question myself whether they actually read the posts before writing or whether they merely look at the subject of the blog post and write the very first idea that drifts into their minds. Anyway, it’s nice to see a clever commentary every now and then in contrast to the same old blog garbage. thx for standing out

  8. Cyrus Majcher

    Appreciating the commitment you put into your blog and detailed information you offer. It’s good to come across a blog every once in a while that isn’t the same old rehashed material. Great read! I’ve bookmarked your site and I’m adding your RSS feeds to my Google account.

  9. Fredrick Janysek

    I just want to mention I am newbie to weblog and seriously loved you’re website. Likely I’m planning to bookmark your blog post . You definitely come with really good article content. With thanks for sharing your blog.

  10. Excel Pivot Tables

    Aw, this was an extremely good post. Taking
    a few minutes and actual effort to make a
    superb article… but what can I say… I put things off a whole lot and don’t seem to get nearly anything done.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s