Microsoft Excel Introduces Single-Cell Arrays After 40 Years
New features allow lists and nested arrays within individual cells.
1Breaking the One-Value Rule
For 40 years, Excel restricted each cell to a single value. This limitation is now lifted with the introduction of arrays and lists within individual cells. Users can insert lists by selecting 'Insert List' or pressing Ctrl+J, then typing or pasting items separated by commas or semicolons. This change allows for more complex data handling directly within cells.
2Enhanced Filtering Capabilities
Lists in Excel enable users to filter by individual items rather than entire text entries. Referencing a list returns all its values for calculations, such as =B2, which spills values into separate cells. This feature simplifies data manipulation and enhances the spreadsheet's functionality by allowing more granular control over data filtering.
3Native Arrays in Cells
Arrays can now exist natively in cells as values or formula results. They can be any size or shape and even contain other arrays. By wrapping a formula body with braces { }, users can keep the result of any spilling formula in a single cell. This innovation provides greater flexibility in spreadsheet design and data presentation.
4Nested Arrays Support
Excel now supports nested arrays, allowing arrays to exist within other arrays. Previously, formulas producing arrays of arrays would return truncated results or errors. Now, supported formulas return complete nested results, enabling more complex data structures and calculations within a single cell.
5Simplifying Nested Arrays
The FLATTEN function simplifies nested arrays by removing one or more levels of nesting. This function enhances data manipulation by allowing users to streamline complex array structures, making it easier to work with multi-layered data within Excel spreadsheets.
6New HAS Functions
Three new HAS functions check whether values are in an array: HAS(array, value), HASANY(array, values), and HASALL(array, values). These functions return TRUE or FALSE based on the presence of specified values within an array, providing powerful tools for data validation and conditional logic in Excel.
7Braces for Array Control
Braces have long been used to describe arrays in Excel. The new feature extends this behavior by allowing multiple layers of braces, enabling users to control array spillage. Wrapping an original array with braces creates a 1x1 array around it, keeping the array in a single cell instead of spilling to multiple cells.
8Impact on Spreadsheet Design
These updates significantly enhance Excel's flexibility and functionality. Users can now design more complex and efficient spreadsheets by leveraging single-cell arrays and nested arrays. This evolution marks a pivotal shift in how data is managed and presented in Excel, catering to advanced user needs.
Original article: Slashdot — Microsoft Excel Introduces Single-Cell Arrays After 40 Years