After 40 Years, Microsoft Excel Will Add Single-Cell Lists and Arrays (microsoft.com)
- Reference: 0185865846
- News link: https://slashdot.org/story/26/09/26/0227226/after-40-years-microsoft-excel-will-add-single-cell-lists-and-arrays
- Source link: https://techcommunity.microsoft.com/blog/Microsoft365InsiderBlog/put-multiple-values-in-one-cell-with-lists-and-arrays-in-excel/4559395
"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.
[1] https://techcommunity.microsoft.com/blog/Microsoft365InsiderBlog/put-multiple-values-in-one-cell-with-lists-and-arrays-in-excel/4559395
Think of all the murders.. (Score:2)
That could have been prevented with this feature
Re: (Score:2)
> That could have been prevented with this feature
Think of all the suicides that will be caused because of it. :-)
SPREAD sheet. (Score:3)
not hidden data sheet. All references to calling the output a spreadsheet should be removed from the product. The point used to be all data points spread into one per cell so it is *easy to follow and print out.
* 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
Oh noes (Score:3)
Now CPAs and small business people will create even uglier multidimensional monsters instead of using DBs
Embrace, Extend, Extinguish (Score:3)
Will this force the alternatives to add this 'feature' also?
Congratulations Microsoft! (Score:3)
Why not just call them multivalues as you've just reinvented the Pick Database from the 80's.
Unforeseen consequences (Score:2)
straight ahead.
Re: (Score:2)
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