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 Dynamic Array files that go into detail. 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 of showing this.

Don’t worry about understanding Dynamic Arrays immediately. In working with them for just a short while 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 has 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:

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

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

Does your version of Excel support Dynamic Arrays?  They were introduced in version 2019 and Excel 365.

You will also see the term Modern Excel.  Since about 2019 Excel has been continuing to add new features and functions that are significant updates to Legacy Excel.  You will see these throughout this website.

Author: Jim Colville

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