Microsoft Excel tests lists and arrays with multiple values in one cell

Microsoft is testing Excel features that can store multiple separate values in a single cell. The update includes lists, arrays and nested arrays.

The previews are rolling out through the Beta Channel for Excel on Windows and Mac. Microsoft does not recommend using them in important workbooks, because the features may change before general availability.

Lists keep values separate

Lists lets users store several items in one cell without turning them into a single block of text. Individual values can be filtered, used in calculations and expanded into other cells when needed.

To create a list, users select Insert > List or press Ctrl+J, then enter items separated by commas or semicolons depending on regional settings. Individual entries can later be edited by double-clicking the cell or pressing F2.

Arrays and four new functions

Arrays can now exist as values or formula results inside one cell, with different sizes and shapes. Nested arrays can contain other arrays, while formula results can be kept in one cell using braces.

Microsoft added four functions for working with these structures: FLATTEN, HAS, HASANY and HASALL. FLATTEN removes nesting levels, while the HAS functions check whether one, several or all specified values are present.

Microsoft illustrates the feature with a running tracker that stores varying numbers of distance segments in kilometers in one row while calculating statistics.

Preview limitations

Lists and arrays are not yet fully supported by conditional formatting, data validation, charts, PivotTables, Power Query or Find and Replace. Calculations involving nested arrays require Compatibility Version 3.