After 40 Years, Microsoft Excel Will Add Single-Cell Lists and Arrays (microsoft.com) 65
Microsoft's senior product manager for Excel acknowledges that "Throughout Excel's 40-year history, you've only been able to put one value per cell." But that's now changing with arrays in cells (as well as nested arrays) and lists.
- "You can create a list by selecting Insert > List or pressing Ctrl+J, then typing or pasting items separated by commas or semicolons, depending on your regional settings. Selecting the icon in the cell shows the individual values..."
"With lists, you can filter by one or more individual items instead of whole text entries. Referencing a list returns all its values for calculations. For example, =B2 spills those values into separate cells..."
- "For the first time in Excel, arrays can exist natively in cells as values or as formula results. They can be any size or shape and can even contain other arrays. You can now keep the result of any spilling formula in a single cell by "wrapping" the formula body with braces { }."
"Since the introduction of dynamic arrays, array results have spilled across cells — for example ={1;2;3}. Wrapping the original array with braces creates a 1x1 array around it, so instead of spilling to multiple cells, the array stays in a single cell. Braces have long been used to describe arrays in Excel and this extends that behavior by allowing multiple layers of braces. This gives you more flexibility when building spreadsheets. Instead of leaving room for a formula to spill, you can keep the result in one cell."
- "Arrays can now also 'nest' inside other arrays... Previously, a formula that produced an array of arrays would return a truncated result or #CALC! error. Now, supported formulas return the complete nested result... FLATTEN(array, [pad_value], [levels]) simplifies nested arrays by removing one or more levels of nesting..."
- Three HAS functions check whether values are in an array:
— HAS(array, value) returns TRUE if value appears anywhere in array, and FALSE otherwise.
— HASANY(array, values) returns TRUE if any of the values appear anywhere in array, and FALSE otherwise.
— HASALL(array, values) returns TRUE if all of the values appear anywhere in array, and FALSE otherwise.
Unforeseen consequences (Score:5, Insightful)
straight ahead.
Re:Unforeseen consequences (Score:5, Insightful)
Oh man, right now I'm so glad my role changed a few years back. I used to have to also cover some web stuff, and people would frequently send me information stupidly-formatted in Word or in Excel (the weirdest one is how people would want an image updated, and they'd send it embedded in a Word document - a Word document containing only the photo).
I am certain people will now be pasting crap, probably unintentionally, into single cells - thanks to this new "feature". And poor web schlubs everywhere will have to waste lots of time trying to tease out the bits of data they actually need.
Re: (Score:3)
Oh man, right now I'm so glad my role changed a few years back. I used to have to also cover some web stuff, and people would frequently send me information stupidly-formatted in Word or in Excel (the weirdest one is how people would want an image updated, and they'd send it embedded in a Word document - a Word document containing only the photo).
I am certain people will now be pasting crap, probably unintentionally, into single cells - thanks to this new "feature". And poor web schlubs everywhere will have to waste lots of time trying to tease out the bits of data they actually need.
Good god, this does seem like a disaster in the making. Glad I use Libre Office. Will we have cells in cells all the way down? It is supposed to be a spread sheet, understandable, for crissake!
Re: (Score:1)
Good god, this does seem like a disaster in the making. Glad I use Libre Office. Will we have cells in cells all the way down? It is supposed to be a spread sheet, understandable, for crissake!
"It's full of cells!"
Re: (Score:2)
Good god, this does seem like a disaster in the making. Glad I use Libre Office. Will we have cells in cells all the way down? It is supposed to be a spread sheet, understandable, for crissake!
"It's full of cells!"
And it occurred to me - I've spent a fair time in meetings with people that use excel to promote their proposals or progress. This hidden info in cells will make that useless, trust in the numbers gone, or at least a lot of time spent explaining and showing whatever they put in the single cell. I haven't used Excel in a while since I use LO, but sounds like I might be able to place an entire spreadsheet into one visible cell.
Re: (Score:2)
Coming up next would be an Excel file within a cell. Maybe start w/ just a single sheet within the cell, followed by an "upgrade" some years later - an entire Excel file (not a file URL, mind you) within the cell
Also, what would be the scope of the list? How many rows & how many columns?
Re: (Score:2)
Coming up next would be an Excel file within a cell. Maybe start w/ just a single sheet within the cell, followed by an "upgrade" some years later - an entire Excel file (not a file URL, mind you) within the cell
Also, what would be the scope of the list? How many rows & how many columns?
Ah, you beat me to it. I just commented much the same, while catching up on posts, but your post was last night. But yes, this whole thing sounds like fixing a problem that doesn't exist, and making problems.
I dunno - could be my perspective. I use LO spreadsheets to make things clear not obfuscate them.
Re: (Score:2)
You already can (almost) have an Excel file in an Excel cell. OLE Spreadsheet objects can be inserted into a a spreadsheet.
It seems the "Insert Object" command does not list Excel worksheet when invoked from Excel itself, but such an object an be created in Word and cut/pasted into Excel.
The whole point of the spreadsheet itself is that it is a set of visible arrays; the feature just lets people bury the whole visible calculation aspect of spreadsheets. Yuck.
Re: (Score:2)
it will be great for doing data analyses, you can now make everything green.
Re: (Score:2)
If it was a truly object oriented system, you could put a spreadsheet into a cell :P
Re: (Score:2)
Re: (Score:2)
"No matter what the customer asks for, what they really want is Excel".
Some people even manage to apply this database program as if it was a spreadsheet, would you believe it?
Think of all the murders.. (Score:2)
Re:Think of all the murders.. (Score:5, Funny)
That could have been prevented with this feature
Think of all the suicides that will be caused because of it. :-)
Will it finally... (Score:1)
Will it finally stop popping up every Excel window to the top of my tab order when I open an Excel file from File Explorer? There are so many quirks in Excel that make it too annoying to use.
SPREAD sheet. (Score:4, Insightful)
* yea I know, that ship has sailed.
Now how does it print the array when someone asks for a 'print out' of those few cells and gets two reams of data listed in 'one cell'.
21st century (Score:2)
Finally arrived also for Excel
Re: (Score:2)
Re: (Score:2)
Oh noes (Score:5, Insightful)
Now CPAs and small business people will create even uglier multidimensional monsters instead of using DBs
Re: (Score:2)
Maybe Grist [howtogeek.com] would be better.
Re: Oh noes (Score:2)
Re: (Score:2)
The real reason office users use Excel for this is because Access is complete dog shit.
It's not complete dog shit. But you have to understand relational databases to use it properly, and most Access users coming from Excel do not, and end up making even bigger messes than they would have with Excel.
Anyone with reasonable knowledge of SQL and VBA, and enough common sense to design a UI properly, can do quite nicely with Access (within its admittedly dogshit limitations, mostly having to do with the fact that it's a Microsoft product and intimately connected to the horrendous Office ecosystem)
Re: (Score:2)
Sounds like you're not defending Access so much as ODBC.
Re: (Score:2)
Access isn't just the database engine, it's also everything else. Mostly I was defending everything else. But the Access database engine is probably less janky than everything else, as long as you use it judiciously.
Re: Oh noes (Score:2)
Embrace, Extend, Extinguish (Score:2, Interesting)
Re: (Score:2)
next they'll install a native APL interpreter.
Re: (Score:3)
Maybe. I read the summary (I know) and immediately thought: Microsoft have just discovered PHP.
Re: (Score:2)
>"Will this force the alternatives to add this 'feature' also?
I came to pose the same question. What does this mean for Calc? Generally, maintaining compatibility with Excel is a must, even if it is offensive. Even if it causes all kinds of issues.
Re: (Score:3)
Calc is not compatible with excel. There's some 60 functions (nearly 15%) which are unique to one or the other or implemented in a different way, and Calc doesn't support VBS (which is offensive).
It was never fully compatible.
The reality is most functions simply aren't used. I suspect this one won't be used much either as there's so much scope to break things.
Re:Embrace, Extend, Extinguish (Score:4, Insightful)
Will this force the alternatives to add this 'feature' also?
LOL. Every time Microsoft touches features someone blindly says EEE without thinking. But in this case it's even more absurd since all alternatives have never been feature / function identical. Are you going to accuse LibreOffice of EEE because it supports 25 more functions than Excel does, while implementing 30 functions which are different to Excel, and while Excel previously had 30 unique functions as well?
The alternatives have never been feature comparable for every individual item. In fact in terms of compatibility in businesses the biggest feature of Excel (office in general) was never implemented: VBS. The corporate world remains hopelessly addicted to horrendously coded macros.
I think in reality most people will ignore this feature. It's simply a bad idea and breaks the function of a spreadsheet.
Congratulations Microsoft! (Score:4, Interesting)
Re: Congratulations Microsoft! (Score:2)
This is going to be awesome (Score:3)
I am totally looking forward to auditing a spreadsheet that loads slow, only to find that it contains an entire copy of the production database in multiple cells. Whatever could go wrong?
Multi-value spreadsheets. (Score:2)
Okay, that works.
Excel is an abacus or a slide rule (Score:1)
People can and do get *scary* good at using it to do all sorts of things. But it's still a limited tool compared to a general purpose digital computer.
Graphing still terrible (Score:4, Insightful)
How did a 3rd dimension get added before graph zoom/pan? For a program that many people use for graphing, the lack of basic zoom/pan is impressive.
Yo dawg! (Score:5, Funny)
We put spreadsheets in your spreadsheet.
Re: (Score:2)
You got peanut butter in my chocolate!
Re: (Score:3, Funny)
I do hope this is implemented recursively so you can put a whole fully functional sheet into any cell. Just wait until suckers get trapped half a dozen levels deep in the labyrinth.
"You are in a twisty maze of PivotTables, all the same."
Re: (Score:2)
Here's a T-shirt for you!
https://www.amazon.com/Spreads... [amazon.com]
I've been doing this for decades. (Score:4, Interesting)
Just put commas or json in there. Whoop-de-doo.
Re: (Score:1)
Re: (Score:2)
Yeah but Google Sheets could do it, assuming I wanted it bad enough to use Javascript.
Timing? (Score:3)
It otherwise seems like a kind of weird time to do it. In principle what a 'cell' is has never really had an obvious mandatory scale: it has essentially always been relevant that a 'cell' existed in relation to other cells, so that something like an array could be a row; but it has also long been allowed and encouraged to do things like substring operations that acknowledge that cell contents are 'larger' objects that contain smaller ones; and are not automatically spilled out for that reason alone. Not clear that the pragmatic use case for spreadsheets would ever have supported some sort of 'any data type that implies it can be decomposed into elements shall be' rule, strings getting broken up into substrings and so on; but it's also not clear that the pragmatic use case is going to be much clarified by a single cell being n levels deep of lists and arrays of lists and arrays being a fully supported thing rather than something you hack on by shoving JSON into text cells or whatever.
It definitely seems like asking for confusion when some, but only some, "excel spreadsheets" have cells that are arrays, potentially of arrays, while others have rows of cells with contents that, at least formally, are not acknowledged as arrays.
Re: (Score:2)
> It otherwise seems like a kind of weird time to do it.
It's for AI. Of course it is. Microsoft is busy cramming LLM's into everything they can.
Instead of (ugh) having the LLM manually cycle through a row or column of cells to get or set the data required, all the data is in one handy object in a single cell that can be trivially pumped in and out of whatever LLM-du-jour takes your fancy.
What I would suggest (Score:2)
Re: (Score:2)
You mean like TreeSheets?
Re: What I would suggest (Score:2)
Re: (Score:2)
That is pretty cool!
Now we only need an Obsidian plugin for it!
Is this really a good idea? (Score:4, Insightful)
What could possibly go wrong? (Score:3)
This new feature makes me think of the "Spring Surprise" by the Whizzo Chocolate Company. This "Cell Surprise" by Microsoft.
one spreadsheet to rule them all (Score:2)
Hooray!
Finally, the first intro sheet can actually contain all other sheets. I'll just put each in one cell.
Wait a moment, now I can put all sheets of all my Excel files inside that one sheet!
Brb
csv (Score:2)
Microsoft finally copied Burroughs (Score:1)
Excel is becoming a dinosaur (Score:2)
In terms of spreadsheet capabilities, Excel has remained pretty much stuck in the 90's for a long time. Microsoft has been so intent on adding things like multi-editing and AI, that the core functionality has languished.
Excel's formulas are notoriously difficult to compose and read. Adding something as simple as a multi-line formula editor, would go a long way. And the need to nest parentheses within parentheses make it really difficult to know what your current parenthesis matches to. The color coding help
program si, excel no (Score:2)
Today, it is easier to have an AI write you a program than it is to figure out how to use excel.
This is useful for . . . (Score:1)
Arrays within cells are potentially useful for encoding hierarchical representations that directly model the physical world.
For example, a car transmission has an array of multiple shafts, the shafts have arrays of gears and bearings, and bearings themselves have two rings and an array of rollers. A real world use case is modeling what happens to the bearing’s life if there’s a scratch on just one of the bearing’s rings or rollers
Try do the same by cramming such data into a two dimensional
Re: (Score:2)
Re: (Score:1)
Mostly agree. A spreadsheet can still be useful for a quick "one off" provided the problem isn't too complex. My example in particular was poorly chosen in that respect - it's intended to be illustrative, not literal - as bearing life analysis usually isn't trivial.
Bugs worked out this millennium? (Score:1)