Microsoft loosens Excel’s one-cell-one-value rule with lists, nested arrays and four new functions

Microsoft said Excel is getting three new capabilities — lists, in-cell arrays and nested arrays — along with four functions: FLATTEN, HAS, HASANY and HASALL. The change relaxes a rule that has shaped Excel for four decades: one cell holding one value. With the update, a single cell can contain multiple entries while still allowing each item to be filtered and calculated separately. Microsoft said users can create a list through Insert > List or by pressing Ctrl+J, then enter multiple values separated by commas or semicolons depending on regional settings. When referenced in a formula such as =B2, those values can spill into adjacent cells. The company also said arrays can now live natively inside a single cell, including nested arrays such as ={{1,2,3};{4,5,6}}, which previously could return truncated results or a #CALC! error. The features are still in preview. Microsoft advised users not to rely on them in important workbooks and listed six known limitations, including gaps in conditional formatting, data validation, charts, PivotTables, Power Query, and Find and Replace. The rollout is limited to Beta users on Windows version 2610 (build 20520.20000) or later and Mac version 16.114 (build 26092111) or later, with a gradual release schedule.