Overview
- Microsoft has released lists, arrays-in-cells, nested arrays and four new helper functions to Excel Insiders on Windows and Mac Beta channels.
- Lists and arrays-in-cells let a single cell store multiple related values while keeping those values available for filtering, formulas, and analysis by wrapping spilling formulas in braces to keep results inside one cell.
- The new functions FLATTEN, HAS, HASANY and HASALL help users simplify nested arrays and test whether values appear anywhere inside lists or arrays.
- Microsoft also shipped Compatibility Version 3 as the calculation mode required for most nested-array operations and lets users set a workbook’s compatibility version if behavior differs.
- The Beta has concrete limits: conditional formatting won’t inspect item contents unless driven by a formula, data validation cannot use lists or arrays for dropdowns, charts do not expand arrays into data points, PivotTables and Power Query do not accept array-valued columns, and Find & Replace cannot edit list items, so Microsoft advises against using these preview features in important workbooks and asks Insiders to send feedback.