Excel Breaks One-Cell Rule with Lists and Arrays

Microsoft is testing a new version of Excel that breaks away from its traditional one-cell, one-value model. According to a blog post by Jake Armstrong, senior product manager for Excel, users can now store multiple values in a single cell using lists and arrays.

The feature allows users to create lists through the Insert > List option or the Ctrl+J shortcut, which treats individual entries as separate items instead of one text string. Filtering and calculations can use these individual entries, and formulas that reference the list will spill its items into separate cells.

Arrays go further by enabling Excel to store an array inside a single cell, whether it's typed in as a value or returned by a formula. This allows users to handle different dimensions or contain other arrays within an array. Previously, nested arrays would produce shortened results or #CALC! errors, but Microsoft says supported formulas now return the full nested result.

Four new functions have been introduced to handle these new structures: FLATTEN removes levels of nesting from an array, HAS checks whether an array contains a specified value, HASANY looks for any one of a set of supplied values, and HASALL confirms that all supplied values are present. However, some parts of Excel do not yet work with the new cell types, including PivotTables, Power Query, charts, data validation, and Find & Replace.