Microsoft Excel multi-value cell update

Microsoft is testing and previewing a significant update to Excel that allows for multiple values within a single cell, departing from the traditional one-value-per-cell model.

The update introduces three primary features: Lists, which allow users to input multiple items separated by commas or semicolons while maintaining filtering capabilities; Arrays in Cells, which allow arrays to exist natively within a cell; and Nested Arrays, which permit arrays to contain other arrays to prevent errors. Users can create these lists via the ‘Insert > List’ menu or the ‘Ctrl + J’ shortcut.

To facilitate these changes, Microsoft has introduced four new functions: FLATTEN, to simplify nested arrays; HAS, to check for a specific value; HASANY, to check if at least one of several values is present; and HASALL, to check if all specified values are present.

These features are currently available as a preview for Microsoft 365 Insider Beta channel users on both Windows and Mac platforms. While the update increases flexibility for complex data modeling and aims to reduce the need for auxiliary tables, some experts note potential impacts on auditability and the need for updates to external software libraries. Current limitations exist regarding Power Query, pivot tables, and conditional formatting. The features remain in preview and may undergo changes before a full release.