Microsoft is changing one of the familiar rules of Excel: a cell is no longer limited to containing just one value. The company has introduced lists and arrays within cells, along with nested arrays, allowing users to store multiple pieces of information in a single cell while keeping each value separate rather than combining them into a single block of text.
اضافة اعلان
The features are currently available for preview through Excel’s Beta Channel on Windows and Mac. Microsoft says the way they work may change before they are released to everyone and advises users not to use them in important workbooks at this stage.
Lists Make Managing Multiple Values Easier
The simplest part of the update is the new Lists feature. Instead of distributing values across separate cells or combining them into ordinary text separated by commas, users can store multiple independent values within a single cell.
For example, users could enter the names of several people responsible for a project in one cell or record multiple dates as part of a survey response. Excel keeps the list items separate internally, allowing them to be filtered individually and used in calculations.
To create a list, users can select Insert > List, or press Ctrl+J, then enter or paste the items, separating them with commas or semicolons depending on regional settings. Items can later be edited by double-clicking the cell or pressing F2.
The feature also changes how filtering works. Instead of treating comma-separated cell contents as a single block of text, Excel can filter based on individual items. Likewise, referencing a list in a formula can return all of its values, which can then be distributed across separate cells when needed.
This could make it easier to organize spreadsheets containing project task assignments, survey responses, and other types of multi-value data.
Arrays Enable Broader Changes
Microsoft is also introducing the ability to place arrays directly inside cells, including nested arrays. An array can be a stored value or the result of a formula within a single cell and can come in different sizes and shapes. Users can also enclose the result of a formula that would normally spill across multiple cells in curly braces to keep the entire result inside a single cell.
Nested arrays allow arrays to be placed inside other arrays. According to Microsoft, some formulas that previously returned truncated results or the #CALC! error when handling arrays containing other arrays can now return the complete nested result.
To work with these structures, Excel adds four new functions: FLATTEN, HAS, HASANY, and HASALL. The first removes levels of nesting, while the other functions check, respectively, whether a particular value, any of a set of specified values, or all specified values exist within an array.
For advanced users, the change could open the door to new spreadsheet designs. Microsoft gives the example of a running log in which kilometer split times for each lap can be stored in the same row, regardless of how many splits there are, with statistics calculated alongside them.
However, limitations remain. Conditional formatting, data validation, charts, PivotTables, Power Query, and Find and Replace do not yet fully support list and array values. The features currently require specific versions of the Beta Channel, while nested array calculations require Compatibility Version 3.
For now, Microsoft considers these additions preview features, and they have not yet reached their final form. But if the rollout proceeds smoothly, the simple spreadsheet cell could soon be capable of handling far more than before.
Resource: Al Ghad
