Microsoft cells out, crams multiple values into Excel boxes

Microsoft has ended 40 years of single-occupancy for Excel cells and is now allowing multiple values to inhabit the same home. "One of the oldest rules of spreadsheets: one cell, one value," said Jake Armstrong, senior product manager for Excel, in a LinkedIn post. "No longer!" "I'm excited to announce a set of new features: Lists, Arrays in Cells and Nested Arrays in Excel." For the time being, this capability is only available in Microsoft Excel for Windows and Mac Beta Channels. And it is opt-in. To make the case for lists in cells, Armstrong in a blog post explained that each Excel project can have multiple owners. "Traditionally, you'd need separate columns, helper tables, or text like 'Carlos, Henrietta, Jacob' packed into a single cell," he explained. "With lists, you can keep those values together in one cell while still working with each item individually. Project owners stay connected to the project they belong to, while remaining available for filtering, calculations, and analysis." And he goes on to cite the potential utility of arrays in cells and nested arrays for expanding the kinds of information that Excel spreadsheets can represent. Armstrong's enthusiasm for multi-value cells comes with a caveat that this is a preview feature and shouldn't be used in important workbooks until general availability. Among those commenting on Armstrong's LinkedIn post, several people suggested the change has the potential to break things and hinder auditability by making business logic less visible. External software libraries that expect cell.value to return only a string, number, boolean, or date may need to be updated to handle a new data type - though Microsoft hasn't yet published how lists, arrays in cells, and nested arrays will be represented in .xlsx XML. This includes libraries like openpyxl, pandas, and calamine. The same goes for applications that import Excel data and don't yet have any concept of multi-value cells, like CSV export, Google Sheets, LibreOffice, and older Excel builds. And users of Excel may need to flatten lists and arrays to make multi-value cells work with existing features like PivotTables, at least until those features have been adjusted to accommodate the new cell structure. (R)