Our Favourite formula is INDEX. Here’s How Microsoft Changed It In 2020 With Dynamic Arrays

There are over 450 different formulas in Microsoft Excel. And our all-time favourite is INDEX.

In January 2020 and Excel 365, Microsoft made a significant change to the way many formulas behave, including INDEX. For people using our Fast Excel Development Method this is an important update that may have substantial effect on the way that your workbooks perform.

The main take-away from this: If you are on Excel 365 use @INDEX in place of INDEX. Do this and you will be fine – Your Fast Excel Workbooks will run fast and clean.

Want to know why? Seen “#SPILL!” in a cell and wondered what it means? Then read on because this is a story of INDEX and dynamic arrays.

What is INDEX and why is it so great?

INDEX returns the value of a cell that sits in a range, given the row and/or column number. If you feed the formula a range and a row (and column) number then the formula will give you the value that sits in the cell.

Here is the definition of the formula. INDEX(array, row_num, [column_num])

In this context, “array” is a range of cells. row_num is a number that refers to the row number of that range. Column_num is the column number, not required if you have a range that is one column wide.

For example, here is a list of fruit in the range of cells B2:B11. The formula =INDEX(B2:B11,D3) returns the item in that cell defined by the row number given in D3. When the value in D3 is 6, the formula returns “Watermelons”.

In our Fast Excel Development Method, we use INDEX extensively because it is a fast and reliable way to join two tables of data.

This simple example is on one sheet. However, the power of INDEX is that it creates a way to pull data from different tables across 1000’s of records and quickly build out a powerful system.

Let’s say you use VLOOKUP formula a lot, and then one day, you learn INDEX (together with MATCH). You will never use VLOOKUP again. Here is an article that I wrote about INDEX as the most useful function you will ever learn.

Here is another example using it to join the Item Master and Sales Orders to bring the Item Description alongside the sales order record.

What has changed in Excel 365 in 2020 and what is a Dynamic Array?

In January 2020, Microsoft introduced Dynamic Array formulas. Array formulas return an array of values rather than a single reference. As such, they are not new, but you had to do a ctrl-shift-Enter like some kind of ninja trick and they appeared with {curly brackets} around them.

Now, Array formulas have become more prominent and are easier to perform. And with Dynamic Array Formulas, you can return a range by just typing in one cell. Like this new formula: SORT.

=SORT(B2:B11) in cell E2 creates a sorted list starting in E2. You get a range for the price of a cell!

There is a good, in-depth review of the new formula here at ExcelJet.com.

The new formulas are not backwards compatible with older versions of Excel. Even if you create them in Excel 365, they will not work in Excel 2016 and before.

For this reason, we won’t be using them in the standard downloads and tools that we provide, or in the Fast Excel Development Template that we offer.

Some of these new formulas are pretty cool, but you won’t need to learn them to keep up-to-date with our methods.

The issue is that INDEX has changed to become a Dynamic Array too, if used with a zero in place of the row number.

If used with a zero, INDEX will return a whole array of cells directly below. So, =INDEX(B2:B11,0) returns an array that lists all the values in B2:B11.

If those cells are blocked, then you get a SPILL! error. See the animation for a comparison.

This is an issue with the Fast Excel method because in large volumes, array formulas will work much more slowly than normal reference formulas.

If there is ambiguity about whether it is an array or a reference, then Excel will go into some kind of slow-mo brain tangle.

The result will be unnecessarily slow workbook. And if you are calculating with 10,000’s or 100,000’s rows then INDEX formulas that refer to a zero means the whole routine can take 30 minutes when normally they would do it in 8 seconds!

Here is a real example.

This is an Advanced Capacity Planning and Scheduling tool that we were building as part of our course: How to Build Planning Tools in Excel.

We built a Capacity Planning tool that exploded 5500 BOM records and calculated the capacity required for every manufactured item.

In the ScheduleCalcs sheet, there was a reference to the Routing. And the Routing Row appeared in a Pivot Table, as you can see in this screen shot.

In the pivot template, a reference to a pivot table will return an empty cell in row 8.

An empty cell returns zero in a cell reference. And we use row 8 to set the formula.

Excel 365 will resolve this as an array formula. Row 10 has the table headings, so this will block the array, giving a #SPILL! error. When it is pasted down it is blocked in every cell and has to resolve all those errors. And even more critically, other sheets that have INDIRECT formula will be very much slowed down.

I realise this is quite deep into Excel technique, so I won’t go further and explain why this happens.

It is enough to know that if you have Excel 365 and you use INDEX then you might have a problem with slow workbooks. And if you use INDIRECT and other volatile functions then this problem will be made much, much worse.

Now, I want to give you the solution. And fortunately it is very simple. As simple as adding a single character.

How to resolve the INDEX problem

To avoid this problem, you just need to add “@” before the INDEX formula.

Because “@” or the “AT-sign” is a “Implicit Intersection Operator“. See? That clears it up then.

Oh. That’s not any clearer? No, neither was it for me until I did more reading.

Apparently “@” or the “AT-Sign” forces Excel to read the INDEX formula as one that is meant to return a reference. so it no longer gets confused by the array.

It makes INDEX behave like it always did. Before Microsoft made this change.

The thing is, if you use a lot of INDEX formulas then you can never be sure how the reference in row 8 is going to resolve. At least, not without a lot of laborious checking. So, it is easier to get into the habit of turning every INDEX into a @INDEX.

Just be careful using FIND-REPLACE for that. Because you might end up with a lot of @INDEXINDEX junk in your formulas! And doing a FIND-REPLACE may still have compatibility issues when you then give your workbook to users with older versions of Excel.

If you use the “@” AT-Sign (as a implicit intersection operator, like @INDEX(… ) when you enter the formula the first time around, then your @INDEX formulas will work fast and reliable. With no penalty or negative side-effect.

Excel versions and a Compatibility Check

It is worth saying a few things about backwards compatibility and other versions of Excel.

If you run Excel 365 Installed desktop version, then I recommend you use @ every time you use INDEX. And especially where the formula in row 8 points to a cell with that resolves to zero or to an empty cell. If you see @INDEX in a formula built by someone else, you know why.

If you run Excel 2019 or older, then you can use INDEX. If anyone opens your file on Excel 365, then Excel will automatically convert it by adding a @ “AT-sign”.

If you run Excel 365 online version, then this principle also applies to formula. BUT, macros will not work in the online version. So our Fast Excel Development Method won’t be much use to you. In this case, install it on your machine and then use Dropbox. Or run the installed version and then sync your local files with Excel online.

If you run any version between Excel 2010 and the latest Excel 365 we will continue to support you. Everything that we do will be tested and checked for backwards compatibility. We ignore a lot of newer functions because we are aware that many of our users have older versions and we want to be inclusive.

If you run Excel 2007 or older, we can still develop custom systems for you. But we can’t guarantee that our downloads will work with your version. There are formulas that are so common now that weren’t supported (IFERROR for example) and pivot tables were built a different way in versions before 2007. I think 10 years is a reasonable timeframe to support backwards compatibility. I know there are old, gnarly Excel developers out there who insist on using vintage versions of Excel. But we have moved on to better places 😉

I would be interested in hearing from you if you have 2007 or older and have no plans to change. And want to keep using our methods. Please contact me on [email protected] or here.

If you run Excel for Mac, then right now the Fast Excel Development Template is not fully compatible with Mac OS. If you have version 2016 or older then there is support for Macros. And we are looking into what it would take to make the Development Template fully compatible. But this is work in progress for now.

You can check the compatibility of your Excel 365 file with older workbooks, using the Compatibility Checker – File > Info > INSPECT WORKBOOK > Check for Issues > Check Compatibility. This is proving to be quite a useful function. PArticularly as Windows doesn’t like multiple versions of Excel on the same computer.

We will continue to use Excel 365 Installed version and then test compatibility with Excel versions all the way back to 2010.