Excel Breaks One of Its Oldest Rules

Excel is getting a new list data type. With it, entries such as multiple names or keywords in a single cell can be recognized as separate values. For now, the feature is available only to testers.

Microsoft is currently rolling out a preview of a new Excel feature to testers: lists. A single cell can now contain multiple independent values. Until now, Excel was designed to hold just one value per cell. General availability is expected in the coming weeks, according to Windows Latest.

Users can create a list through the menu option for inserting a list (Insert > List) or by pressing Ctrl+J. They can then enter values manually or paste them from the clipboard. Depending on the regional settings, commas or semicolons are used as separators.

Filters and Formulas Can Access Individual Values

The practical benefits become particularly clear when filtering data. If, for example, a cell contains three names, Excel can now offer each name as a separate filter option. Previously, the entire text appeared as a single entry. If a list contains numbers, formulas can work with those values directly. There is no longer a need to split the values across additional rows or columns, as has traditionally been the case.

Four functions are being added alongside the new list functionality:

• HAS checks whether a specific value exists in a list.
• HASANY checks whether a list contains at least one of several specified values.
• HASALL checks whether a list contains all specified values.
• FLATTEN resolves nested arrays so their contents can be processed further.

For example, a formula such as =HAS(Cell,"Name") can determine whether a particular person is among the responsible individuals listed in a cell.

A Legacy of Excel’s Calculation Logic

The limitation of storing only a single value in a cell was deeply embedded in Excel’s calculation logic. Joe McDaid, a former Excel program manager, once described how this behavior worked. Traditional formulas relied on what is known as implicit intersection. If a calculation returned multiple values, Excel would silently select one of them because the cell could not accommodate the full result.

Microsoft has not explained why it is only now introducing lists and nested arrays.

(Editorial Team)