Microsoft is testing Excel features that let users store multiple structured values inside a single cell. Previously, a cell held one value; formulas that returned several results generally spilled them into neighboring rows or columns. The new options keep those values together within the cell.
The preview includes three structures. Lists hold separate items in a single column and can mix text, numbers, dates and Boolean values while retaining their data types. Arrays contain rows and columns within one cell, while nested arrays let formulas return arrays inside other arrays.
These features could reduce worksheet clutter. For example, a list can keep a person’s areas of expertise in one column rather than requiring a separate column for each skill. Survey responses can also be stored together in one cell per respondent. Nested arrays offer another way to handle formula results: functions such as TEXTSPLIT can keep their outputs inside each row’s cell instead of spilling across the worksheet and potentially triggering a #SPILL! error.
Users can create lists by pressing Ctrl + J in a selected cell or choosing Insert > List, then entering items separated by commas or line breaks. Arrays and nested arrays can be entered with curly braces, and formulas can also generate them. Referencing an in-cell structure with a standard formula may spill its contents onto the grid; functions such as INDEX can be used to retrieve specific items without doing so.
The features are in preview for Microsoft 365 Insider members using Excel for Windows or Mac on the Beta Channel. Microsoft has not announced when they will reach all Microsoft 365 subscribers. They are not supported in Excel for the web or perpetual-license editions such as Office 2021 and Office 2024.
Microsoft cautions that these structures may make data less immediately visible, since users must interact with a cell to inspect its contents. They can also use more memory and slow calculations compared with single-value cells. Workbooks containing them may show formula errors in older or unsupported Excel versions, so collaborators may encounter compatibility problems.
Most calculations involving nested arrays require Compatibility Version 3, which can be set under Formulas > Calculation Options. Excel has also added FLATTEN to unpack nested arrays into worksheet rows and columns, along with HAS, HASANY and HASALL to check for values in an array. Some traditional functions, including SUMIF, COUNTIF and VLOOKUP, may not evaluate values inside multi-value cells without suitable wrappers or newer alternatives. Microsoft says the features’ behavior may change in response to Insider feedback.
Sources: Computerworld.