In these pages and throughout this website, you will see reference to Legacy Excel and Dynamic Arrays.  Legacy Excel is reference to Excel as we know it. Dynamic Arrays refer to the “new” Excel where Dynamic Arrays are the “new and improved” way to work with Excel.  We haven’t found easy to understand wording to describe Dynamic Arrays as it gets technical very fast in a “your eyes will glaze over” kind of way. Reviewing the file below will help as a starter and then get into the other files. You’ll see real quick the amazing new way to work with Excel.

Here’s one quick description. In Legacy Excel you enter a formula in a cell and copy to the desired range. With Dynamic Arrays, you select the range with the underlying data, enter a math sign, perhaps to multiply ( * ), and select the next range and then press Enter. Excel will fill, or Spill the results into the range upon pressing enter. The file below does a good job showing this.

Don’t worry about understanding Dynamic Arrays immediately. In working with them for just a short awhile you’ll all of a sudden “get it” and will be off and running.

Another attempt at describing Dynamic Arrays:
Throughout over 30 years of its history, Microsoft Excel has undergone many changes, but one thing remained constant – one formula, one cell. Even with traditional array formulas, it was necessary to enter a formula into each cell where you want a result to appear. With dynamic arrays, this rule is no longer true. Now, any formula that returns an array of values automatically spills into neighboring cells, without you having to press Ctrl + Shift + Enter, copy it or do any other moves. In other words, operating dynamic arrays becomes as easy as working with a single cell.

Dynamic Arrays is a different way of thinking from Legacy columns and rows.

The best way to see this is with this Excel File:

DA-Compare-Legacy-to-Dynamic-Arrays.xlsx

Review the ABS vs Dynamic Array and Legacy vs Dynamic Array worksheet tabs as well as the rest of the file

Author: Jim Colville

Date: 5/18/2023 – Updated: 6/9/2023