Dynamic array formulas can automatically populate, or spill, results into neighboring cells without requiring legacy Ctrl+Shift+Enter (CSE) array formulas. When you open a workbook containing dynamic array formulas in a version of Excel that doesn't support them, compatibility issues may occur.
Specifics
- When you enter a formula in a version of Excel that supports dynamic arrays, Excel determines whether the formula can return multiple values, even if it currently returns only one value. If the formula can return multiple values, Excel may save it as a dynamic array formula. In versions of Excel that don't support dynamic arrays, compatible formulas may appear as legacy CSE array formulas. However, formulas that use functions unavailable in older versions of Excel may not calculate correctly.
- The following examples show the MUNIT function entered as a dynamic array formula and as a legacy CSE array formula. Legacy CSE array formulas behave similarly to dynamic array formulas but don't automatically resize or display a spill border. For more information, see Dynamic array formulas vs. legacy CSE array formulas.
Dynamic Array
Legacy CSE
|
|---|
|
|---|
- If you plan to share a workbook containing dynamic array formulas with someone using a version of Excel that doesn't support dynamic arrays, avoid using functions and features that aren't available in that version. Use the Compatibility Checker to identify potential compatibility issues, including dynamic array formulas that may appear as legacy CSE array formulas in older versions of Excel.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.