Excel: Arrays, Tables, and ‘The Twilight Zone’

Editor’s note: Earlier this fall, Microsoft announced a partial rewrite of Excel’s calculation engine. The result is an evolutionary leap that promises to bring numerous improvements to Excel users in the coming years. Already unveiled are a new type of calculation (Dynamic Arrays), seven new functions, and two new errors. These changes have not yet been rolled out to all Excel users, but you don’t have to wait to see what they can do. Excel MVP Liam Bastick is covering all the changes in a seven-part series. In this second instalment, he shows how Dynamic Arrays compare and relate to Excel Tables.   

In the first article in this series, “Excel: Microsoft Unveils Dynamic Upgrades”, we looked at why Dynamic Arrays are a step forward from the old way of handling arrays. We also walked through a simple example of how Dynamic Arrays work. Now let’s go a little bit deeper.

For those joining us for the first time, I recommend that you read Part 1. But as a quick reminder, Dynamic Arrays provide a much-simplified approach to doing arrays compared with the traditional approach, which required copying the formula being applied to the array into the entire range and then, for formulas returning more than one result, having to press Ctrl+Shift+Enter to see the subsequent results.    

So what do Dynamic Arrays look like in action? Consider the example shown in the screenshots below. As you can see, the formula entered into cell F33 in the right image is =F12:H27, which references the original data shown in the left image.

Did you notice that I am not having to anchor cells (ie, use dollar [$] signs)? The formula just spills (ie, it automatically extends into the designated area — in this case, three columns by 16 rows). Let me be clear. If I select cell F34, I get the following:

Here’s a first. Check out the formula in the formula bar. It’s greyed out. Ever seen that before? Effectively, cell F34 contains the value “Triangle”, but it does not actually contain an Excel formula in the usual sense. To demonstrate this, let me show you the VBA Immediate window:

To quote MrExcel himself, Bill Jelen, similar to Schrödinger’s cat, if you select cells F33:H48 and use Go To Special (F5 > Special), and then select Formulas, cells F33:H48 are shown as formula cells. You can even copy and paste them as values. Ladies and gentlemen, welcome to “The Twilight Zone” (cue eerie music).

Unlike legacy array formulas, Dynamic Arrays automatically resize if you add or remove data from the source range. So, you might be thinking, “Hey, that sounds similar to an Excel Table. How do Dynamic Arrays relate to Tables?” The answer is simple: You cannot use Dynamic Arrays in a Table, but Dynamic Arrays may refer to a Table.

In this above illustration, cells F57:H72 have been converted into a Table (Ctrl+T), with the Table named Basic_Array_Example. In cell L57, I have simply typed “=” and then highlighted the entire Table. It was all replicated.

The advantage of linking a Dynamic Array to a Table is clear:

I can add rows and/or columns, and the Dynamic Array will update automatically.

Talking of varying sizes, it’s clear to see one potential issue with Dynamic Arrays. If we are not referring to a Table, what happens if the source data change dimensions? This may be why you should refer to a Table for safety.

However, once you have a Dynamic Array, referring to it is simple using what is known as the Spilled Range Operator. For example, if I want to refer to the Dynamic Array in the previous examples, it initially had a range of L57:N72. However, once I had added a row and column to the Table, this resized to L57:O73. I can easily refer to this array, whatever its size, as follows:

In its initial state, the formula =L57# allows for variations — you simply type in the top left-hand cell reference (ie, the cell with the non-greyed-out formula) and add “#”, known as the Spilled Range Operator. Simple!

It’s not all peaches and cream though. Whilst Dynamic Arrays and Tables share some similarities, they are very different beasts. This couldn’t be clearer than when you create charts:

Here, I created two charts when I had only the data up to June. Then, I added the data for July. The chart on the left referencing the Table source data updated instantly. However, the chart on the right still only displayed up through June even though the Dynamic Array had updated. Conclusion: Use Tables, not Dynamic Arrays, as your references for dynamic charts.

You might be asking, “Hey, why can’t you use Dynamic Arrays in a Table?” Simply put, spilled array formulas aren’t supported in Excel Tables. If you try to put one in a Table, you will get one of the two new Excel errors, #SPILL. To fix the problem, try moving your formula out of the Table, or go to Table Tools > Convert to range.

For more on the #SPILL error, check out my next article on the new errors that come with Dynamic Arrays, “Excel: 2 New Errors and a SINGLE Function”. Or if you’d like to start learning about the new functions poised to make a big impact, check out the three Function Focus articles when they go live.

An Excel Evolution: The Series

Getting started

Part 1: Excel: Microsoft Unveils Dynamic Upgrades

Part 2: Excel: Arrays, Tables, and “The Twilight Zone”

Part 3: Excel: 2 New Errors and a SINGLE Function

Function focus

Part 4: This Excel Change is More Than SORT of a Big Deal

Part 5: Focus on 2 New Excel Functions: UNIQUE and FILTER

Part 6: Excel New Function Focus: SEQUENCE and RANDARRAY

Final thoughts

Part 7: Will Dynamic Arrays Kill Data Tables, PivotTables?

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of An Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.