Microsoft Excel to Add Single-Cell Lists and Nested Arrays After 40 Years

The new list feature is accessed by selecting Insert > List or pressing Ctrl+J, after which users can type or paste items separated by commas or semicolons, depending on regional settings, according to the report. Selecting the icon in the cell shows the individual values. The report said that with lists, users can filter by one or more individual items instead of whole text entries, and that referencing a list returns all its values for calculations. It gave the example =B2, which spills those values into separate cells.

For the first time in Excel, arrays can exist natively in cells as values or as formula results, according to the report. They can be any size or shape and can even contain other arrays. Users can now keep the result of any spilling formula in a single cell by "wrapping" the formula body with braces { }. "Since the introduction of dynamic arrays, array results have spilled across cells — for example ={1;2;3}," the report said. Wrapping the original array with braces creates a 1x1 array around it, so instead of spilling to multiple cells, the array stays in a single cell. Braces have long been used to describe arrays in Excel, and this extends that behavior by allowing multiple layers of braces. The report said this gives users more flexibility when building spreadsheets: instead of leaving room for a formula to spill, they can keep the result in one cell.

Arrays can now also "nest" inside other arrays. Previously, a formula that produced an array of arrays would return a truncated result or #CALC! error, according to the report. Now, supported formulas return the complete nested result. FLATTEN(array, [pad_value], [levels]) simplifies nested arrays by removing one or more levels of nesting.

Three HAS functions check whether values are in an array, the report said. HAS(array, value) returns TRUE if value appears anywhere in array, and FALSE otherwise. HASANY(array, values) returns TRUE if any of the values appear anywhere in array, and FALSE otherwise. HASALL(array, values) returns TRUE if all of the values appear anywhere in array, and FALSE otherwise.

The changes affect a core constraint of Excel, which the report described as having allowed only one value per cell throughout its 40-year history. The Slashdot report did not provide a release date, pricing details, or a list of supported Excel versions for the new features.