For simplicity, we use the term "Legacy Excel" to refer to any version of Excel before dynamic array formulas were introduced. Practically speaking, this means Excel 2019 and older, since Excel 365 and Excel 2021 are the only versions of Excel that include dynamic array formulas and new functions like SORT, UNIQUE, FILTER, SEQUENCE, and XLOOKUP.
Dynamic array formulas are a huge change to the Excel formula engine because they let you easily work with multiple values at the same time in a formula. In practical terms, this means array formulas "just work". In Legacy Excel, most array formulas still need to be entered with control + shift + enter.
Example
The LEN function returns the length of a text string as a number:
=LEN("apple") // returns 5
To get a total of all characters in a range, you can wrap LEN inside the SUM function like this:
=SUM(LEN(range)) // total characters in range
This is a simple array formula, but it must be entered with control + shift + enter in Legacy Excel:
{=SUM(LEN(B5:B10))}
Note: Excel will add the curly brackets automatically when the formula is entered with control + shift + enter. Do not add the curly braces manually.
In Modern Excel, the same formula below will work as-is, without special handling:
=SUM(LEN(B5:B10))
The bottom line is that array formulas in Legacy Excel need special handling. In current versions of Excel, array formulas just work.
