Microsoft Tests Lists and Arrays Inside Single Excel Cells
Microsoft is testing a new Excel feature that lets a single cell hold multiple values. It's currently limited to Beta Channel Insiders on Windows and Mac.
Microsoft is testing a version of Excel that lets a single cell hold more than one value, a break from the spreadsheet’s long-standing one-cell, one-value model.
The preview adds lists, arrays in cells, and nested arrays, according to a Microsoft 365 Insider blog post by Jake Armstrong, senior product manager for Excel. The feature is opt-in and currently limited to Microsoft 365 Insiders in the Excel Beta Channel on Windows and Mac.
How Lists and Arrays Work Inside a Single Excel Cell
Lists are the simplest part of the change. Users create one through Insert > List or with the Ctrl+J shortcut, then type items separated by commas or semicolons, depending on regional settings.
The values stay grouped in one cell, but Excel treats them as separate items instead of one text string. Filtering and calculations can use the individual entries, and a formula that references the list, such as =B2, spills its items into separate cells.
Microsoft’s example is a project tracker where one row holds a list of owners without extra columns or a helper table. A list opens from the cell icon, and double-clicking the cell or pressing F2 edits it. “One of the oldest rules of spreadsheets: one cell, one value,” Armstrong wrote. “No longer!”
Arrays go further. Excel can now store an array inside one cell, whether it is typed in as a value or returned by a formula, and those arrays can have different dimensions or contain other arrays. A formula such as ={1;2;3} normally spills into three worksheet cells. Wrapping the formula body in braces keeps the output inside one cell instead.
Nested arrays previously produced a shortened result or a #CALC! error. Microsoft says supported formulas now return the full nested result, which stays available for later calculations.
The company’s sample is a running log in which each row stores an array of split times, so runs with different numbers of splits sit in the same table while Excel still calculates total time, average split, average speed, and fastest kilometer.
Four new functions handle the 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.
- HASALL confirms that all supplied values are present.
Excel Preview Requirements and Current Limitations
Testers need Excel for Windows Version 2610 (Build 20520.20000) or later, or Excel for Mac Version 16.114 (Build 26092111) or later. To try the feature:
- Join the Microsoft 365 Insider program and switch Excel to the Beta Channel.
- Update Excel to the build listed for Windows or Mac.
- Select a cell and use Insert > List or press Ctrl+J to create a list.
Parts of Excel do not work with the new cell types yet. PivotTables cannot use array values as source data, Power Query cannot load or emit array-valued columns, and charts do not turn array entries into individual data points. Data validation cannot use lists or arrays as dropdown values, and Find & Replace cannot change a single item inside a list or array.
Most nested array calculations also require a new workbook setting called Compatibility Version 3. Microsoft warns that this setting can change results in some existing formulas, and workbooks can stay on Compatibility Version 1 or 2 if Version 3 causes problems.
The company has not explained how lists and arrays will be stored in the .xlsx XML format, which matters for applications and libraries that expect a cell to hold a string, number, date, or Boolean value.
Microsoft says the rollout is gradual, that the features may change, be paused, or be removed during testing, and that users should not rely on them in important workbooks until general availability.
