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: To get a total of all characters in a range, you can wrap LEN inside the SUM function like this: This is a simple array formula, but it must be entered with control + shift + enter in Legacy Excel: 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: The bottom line is that array formulas in Legacy Excel need special handling. In current versions of Excel, array formulas just work. Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
