Microsoft's Making Excel's Formulas Even Easier (theverge.com) 47
Microsoft has announced it's making Excel's autocomplete even smarter, at least in the web version that comes with Microsoft 365 (formerly known as Office 365). The Verge reports: Formula suggestions are pretty much what they say on the tin: if you type the equal sign into a cell, Excel for web will try to intelligently suggest what type of formula you should be using, given the data that's around it. For example, if you have a full of quarterly sales numbers and a column at the end labeled "total," Excel might suggest summing the range of cells. According to a blog post from Microsoft, the feature currently only works in English, and will suggest sum, average, count, counta, min, and max formulas. It's not a groundbreaking feature, to be sure -- Google Sheets has had something similar for a while, and Excel's AutoSum has long been a quick way to apply formulas to data -- but for some use cases, it could be a nice timesaver.
Then there's Formula by Example, which is similar to the Flash Fill feature that can automatically detect patterns in data and fill out the rest of a column. The feature is a bit hard to explain succinctly, but this video from Microsoft gives you an idea of what it's about; detecting a pattern where you're combing information from cells and then automatically generating a formula that will save you some typing.
Microsoft's blog post also includes several other feature announcements [...]. There's a function for adding images with alt-text into your tables coming to Windows, Mac, and web, and the company's also adding nested Power Query data types and the ability to get data from dynamic arrays to the Insider version of the Windows app for testing. One other potentially useful (and thankfully easy to understand) feature coming to the web is "suggested links," which will automatically help you fix broken links to other workbooks stored in the cloud.
Then there's Formula by Example, which is similar to the Flash Fill feature that can automatically detect patterns in data and fill out the rest of a column. The feature is a bit hard to explain succinctly, but this video from Microsoft gives you an idea of what it's about; detecting a pattern where you're combing information from cells and then automatically generating a formula that will save you some typing.
Microsoft's blog post also includes several other feature announcements [...]. There's a function for adding images with alt-text into your tables coming to Windows, Mac, and web, and the company's also adding nested Power Query data types and the ability to get data from dynamic arrays to the Insider version of the Windows app for testing. One other potentially useful (and thankfully easy to understand) feature coming to the web is "suggested links," which will automatically help you fix broken links to other workbooks stored in the cloud.
What I would have loved... (Score:5, Interesting)
Back when I used Excel I would have loved something that presented formula blocks rather than just keeping everything on a single line. Troubleshooting long formulas was always a pain.
I know-- don't use it for calculations and presentation at the same time. But, when you had to share documents around it really helped to have them self-contained without a separate presentation sheet or hidden columns.
Now I would much rather process data in a shell script and push output to something else for presentation.
Re: What I would have loved... (Score:3)
One thing that I would like to turn off - the language specific formulas. IF() is translated to OM() in Swedish for example. This makes it next to impossible for me to use the Swedish version even though I'm a native Swede.
Re: What I would have loved... (Score:2)
All programming should be in English â" and I say this as a non-native English speaker. There is NOTHING to be gained from branching every function into 100+ differently called constructs. It also fragments the ecosystem.
Re: (Score:2)
Long ago I was asked by Europeans I was visiting to purchase English versions of Excel for them which could not be purchased locally. Not so much for the formulas but the help which was even less useful after being mangled in translation. (This pre widespread internet, yes, there was such a time.)
Multi line formulas (Score:2)
Excel has always had multi line formulas. Only convention makes them one line illegible. Alt-Return.
Prior to the ribbon it worked well, with the formula bar automatically expanding for multi line formulas. Then the ribbon broke it, you need to manually expand the formula bar, and no indication of a multi line formula. Presumably because most excel users put it all on one line.
Spreadsheet Detective can pretty print formulas.
Re: (Score:2)
Back when I used Excel I would have loved something that presented formula blocks rather than just keeping everything on a single line. Troubleshooting long formulas was always a pain.
I know-- don't use it for calculations and presentation at the same time. But, when you had to share documents around it really helped to have them self-contained without a separate presentation sheet or hidden columns.
Now I would much rather process data in a shell script and push output to something else for presentation.
The number 1 change Microsoft can do to improve formulas is get rid of that annoying pop up box that demands attention whenever a formula is incomplete or wrong. 999 times out of 1000 it's usually because someone has pressed the wrong key whilst going back to fix a mistake.
Data mining (Score:3)
Re: (Score:2)
Not really. You see data is only mined from plebs. This is why there's this dichotomy of Microsoft invading privacy of every single person while at the same time enterprises entrusting them with their most secret of documents (no seriously, we've been banned form using Google Translate due to fear of leaks of secret information, the alternative: Microsoft Translate).
When you pay a lot of money to someone they treat you differently then when you pay your monthly subscription to Office 365 running on your Win
Yeah sure (Score:3, Informative)
Re: (Score:1)
Try abbreviating Converged Network Adapter in word.
Re: (Score:2)
Agreed. Most people don't know you can edit the autocorrect and autocomplete settings, turn off things like replacing normal quotes with smart quotes, add exceptions and ignore words, or just tell the program to ignore ANY word that is in all caps.
Military users found very early that Word would try to correct TWo INitial CApitals, which played havoc with many military ranks, i.e. SSgt, MSgt, PV2, PO3, BGen, etc. Turning that off was one of the first things new users had to do.
Re: Yeah sure (Score:2)
Evil solution: Don't use abbreviations then!
For online office stuff (Score:2)
Much as I dislike Google, I prefer Google's Sheets over Microsoft Excel. But my boss is a Windows guy, and for shared materials he's been pushing us to use MS's online office stuff instead (we're an academic unit and have access to both).
Excel seems to do a bit more than Sheets, but there's nothing in those extra abilities that I actually care about. Excel Online is unwieldy, slow, and often unresponsive. And MS's overall navigation and management interface (Sharepoint, IIRC) is just flat-out awful. It's ha
Re: For online office stuff (Score:2)
Re: For online office stuff (Score:2)
Slashvertisement for M$... (Score:2)
Wow, Microsoft is on top of their game here (Score:4, Funny)
Slightly easier Excel formulae autocomplete...
Notepad getting tabs [theverge.com]...
Holy crap, Microsoft is on fire lately. I can barely contain my excitement!
Disable it? (Score:3)
One would hope this ghastly intrusion would have the option of being disabled. Nothing disrupts a thought process more than a computer offering "suggestions". Get out of my way and let me do my thing.
Re: (Score:2)
Get out of my way and let me do my thing.
You know it you can literally ignore it right? How is it in the way for you when it disappears should you have the audacity to type anything on your keyboard? What's your next battle? A fight with Dell because their logo appears on the bezel of the monitor disrupting your thought process?
Flash Fill Feature (Score:5, Funny)
A Priest, a minister and a rabbit walked into a bar.
The bartender asks the rabbit, "What are you having?"
The rabbit says, "I don't know. I'm just here because of auto-correct."
Re:Flash Fill Feature (Score:5, Funny)
Love it!
And on a related note...
The Inventor of AutoCorrect died.
Condiments are roaring in.
* He will be mist
* He was a very general food man
* He was killed in four luggages
* He is in a wetter place
* Paying for his knife and Emily
* Send flours and dalmations to---
* May he roast in piece
* Funnel will be held tomato.
Re: (Score:2)
"Even easier"? What did they break this time? (Score:3)
Because every time MS "improves" Office or Windows or any of their products, they have been getting _worse_.
*Even* easier??? (Score:3)
Who said Excel formulas were "easy"? I'm a programmer, and I don't find them easy in any way. They're hard to read, the parameters are often unclear, they are all typed on a single line. Try using a VLookup or XLookup without consulting a help page, good luck. And what exactly is the difference between them again?
It would be really hard to make them any _harder_.
Re: (Score:2)
I seems that VLOOKUP and HLOOKUP are being deprecated in favor of XLOOKUP, which apparently you didn't know. Also, I don't need help for knowing V for vertical or H for horizontal, but I will use help often, partly becau
Excel's formula language is an abomination (Score:2)
Agree! Like so much else from Microsoft, their spreadsheet application didn't become a monopoly because of how good or easy it was, but because of their "marketing skills", which have included all sorts of wrongdoing right from the start. The most significant thing that really was 'easy' from very early on was for executives to be able to choose Microsoft products without the necessity to know something about them or even to think any further than 'market share'.
Worse, Excel's monopoly has left later compet
The Spreadsheet Wars were BIG (Score:2)
... in the 1980s. People purchased very expensive personal computers just to do spreadsheets, the alternative being a pocket calculator.
Excel won because it had the WIMPs interface, which Lotus 1-2-3 was slow to implement.
Excel was really very good for the time. And VBA was and still is very good. Tightly integrated into Excel, it was revolutionary. Today all the cool kids use {} instead of EndIfs, but VBA was pretty tight, with a few winkles. And optionally strongly typed, only now being reinvented b
Re: (Score:2)
Agreed, the arcane formulas and cell references were inherited from earlier times, and at the time, they were better than the alternatives. I used that old command-line system in Lotus 1-2-3, and it was indeed worse. I'm sorry the attempt at something better, that you were involved in, failed. It's really hard to take on a giant. Spreadsheets are definitely ripe for disruption.
It might not be hopeless. Google successfully took on Excel and brought something revolutionary to spreadsheets: simultaneous multi-
Re: (Score:2)
The A1 references were actually the brilliance of VisiCalc. Simple and clear, reflecting the visual lay out of formulas. They tried various free form formats, but the Grid is what worked. But it does not scale very well.
Google Sheets (not by google BTW) is almost exactly Excel. They introduced one important formula feature, adopted by Excel, which is array formulas that automatically "Spill". First substantial improvement in 20 years.
Re: (Score:2)
The A1 references (I think up to this day Excel still *does* support an alternative RxCy referencing scheme based on its predecessor 'Multiplan') are the least of Excel's and its formula language's problems and quirkinesses, and it was "very good for the time" only for someone who never really used something substantially better (which Lotus 1-2-3 might not have been, either), like the 'Framework' spreadsheet I mentioned. That said, I do agree that Microsoft's being early in publishing a full spreadsheet ap
Re: (Score:2)
Excel certainly supports RxCx style. But awkward syntax.
The interesting beast was Lotus Improv. Did away with A1 References, copied formulas. Introduced amazing pivot tables. Almost an excellent product back in the 1990s.
But too different, and never quite right.
Re: (Score:2)
Yes, interesting; I remember the name Improv, but I believe I've never had it on one of my own machines... I did have an installation of Lotus SmartSuite on OS/2 at some time, but that included 1-2-3, not Improv (and at some point StarOffice became a very usable product and that's where I mostly went back then)...
It seems something very much like Improv still exists today in a product called Quantrix [quantrix.com], marketed as a "financial and business modelling platform". I've found contradicting sources with regard to
Re: (Score:2)
Indeed, Quantrix is still going, started about 2003, and probably quite good. But tiny usage base compared to Excel. And I am talking about professional financial modelers, not casual spreadsheet users.
I produce
http://www.spreadsheetdetectiv... [spreadsheetdetective.com]
which essentially reverse engineers the structure from the mass of formulas in a complex model. You might enjoy the short animation on that site.
Re: (Score:2)
A solution for messy single-line formulas and long multi-cell calculations is to write a macro function that does the same thing with a more concise entry into the cell. Microsoft is making spreadsheets with macros hard to share lately, though. (Couldn't they have a setting that stops macros that work outside the spreadsheet while letting functions that only affect the sheet continue working?)
Re: (Score:2)
Yeah, VBA macros seemed like a good idea at the time, but the "I Love You" worm illustrated just how insecure and dangerous they were. It does seem they could have made it more secure with some effort, but clearly they didn't want to do that.
Still, suppose they made VBA (or C#) macros work well and securely. That's kind of a large leap, from a simple formula to a VBA project. It seems to me there should be some kind of enhanced formula functionality, that doesn't require a full-blown programming IDE.
perfect timing (Score:2)
This two days after a study find that AI Assistants Help Developers Produce Code That's More Likely To Be Buggy [slashdot.org]. Ah who cares about facts when you can push new features.
Ok, now fix data type recognition (Score:3)
Ok, now fix data type recognition so I don't have to format every cell as text to get it to stop interpreting my input as something it's not
I CAN SEE YOU TYPED A NUMBER, I WILL NOW ASSUME EVERYTHING YOU TYPE IS MONEY. IT'S NOT MONEY? FUCK YOU! NINE HUNDRED DECIMAL PLACES!
Re: (Score:2)
Indeed. There is an ancient glitch that Dates are not actually a separate data type, just a format. But difficult to fix while maintaining compatibility.
Re: (Score:2)
Try that when importing data or copying/pasting. Yes, there are ways to do that, but Excel's unrequested changes often slip through without noticing the few cells that are affected.
Re: (Score:2)
Re: (Score:2)
Interesting complaint.
a) the auto formatting feature can be turned off.
b) Excel never identifies a type as money unless you add a currency to the field.
c) Excel never adds more decimal places than fit in any cell and often defaults to *not enough* for the required precision.
So ... PEBCAK.
Re: (Score:2)
You cannot turn off the autoformatting that changes entries, e.g. "2-1/2", to a date. You can, as was noted upstream, type a single quote (') before entering the number to format it as text, assuming you want it to be text, which you might not.
Excel sometimes identifies a number as currency if there are currencies entries above it in the column.
Re: (Score:2)
Yes, this would be great.
Not everything is a date. Who would type 1-1/2 when intending a date? More importantly, why won't Excel let me customize autocorrect to not convert anything to dates unless explicitly set. (I don't use dates that much)
One time I had a bug where it was converting entries (I believe they were serial numbers) to strange formats which I later f
Garbage (Score:2)
Excel's formula UI dates back about 40 years and is inscrutable garbage if you try to do anything nontrivial.