Implicit intersection and dynamic arrays

In September, 2018 we introduced several new functions in a family of functions called dynamic arrays. These functions will automatically populate a range, or array, and eliminate the need for legacy Ctrl+Shift+Enter (CSE) array formulas. Dynamic arrays do have performance implications with regards to how they manage implicit intersection, which are often used in financial summaries.

Specifics

  • In dynamic aware Excel, Excel will not attempt to do implicit intersection. Rather, it will interpret the formula as an array formula.

  • Implicit intersection was used by non-dynamic array aware Excel to prevent a formula from returning an array. However, now that the grid natively understands arrays, there is no need to silently perform implicit intersection.

  • If you would like a formula to perform implicit intersection in dynamic array aware Excel, you can make use of the SINGLE function.

  • If a formula authored in non-dynamic aware Excel relied on implicit intersection, that formula will be wrapped in SINGLE when opened in dynamic array Excel. The SINGLE will be removed on save to facilitate compatibility with non-dynamic aware Excel.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

FILTER function

RANDARRAY function

SEQUENCE function

SINGLE function

SORT function

SORTBY function

UNIQUE function

#SPILL! errors in Excel

Spilled array behavior

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×