14 ms·
Microsoft is making Excel’s formulas easier
- function_seven 4y agoMy current #1 wish for Excel formula entry: Please let me use [Tab] and [Enter] in the formula bar without trying to commit the change! I like to format my formulas with multiple lines and indentation, but to do that I have to hold down [Alt] and mash the spacebar like a caveman. I am happy with the new features I've been seeing. LAMBDA() and the new TEXT functions are nifty.
- LeifCarrotson 4y agoAnd [Home] without jumping to select column A, and the arrow keys should work consistently instead of sometimes moving the cursor and sometimes inserting a cell selection.
- sokoloff 4y ago@airstrike gave me the following very helpful clue when I complained about this a couple years ago: "Just hit F2 while editing a formula to toggle between Edit and Enter modes, one of which will behave as you expect. The other mode, which you hate, is very useful when you want to add references to other cells into your formula"* There is a designation in the lower left of the window to show whether you're in Edit or Enter mode. * https://news.ycombinator.com/item?id=26388148 https://news.ycombinator.com/item?id=26388148
- function_seven 4y agoTHANK YOU. This doesn't fix my original gripe, but it solves another one I've had. When doing conditional formatting rules, I thought I was stuck with a "dangerous" arrow key. F2 works in those boxes as well to let me position the cursor instead of clobbering my formatting rule with cell references.
- LeifCarrotson 4y agoAh, I usually begin editing a pre-existing cell by tapping F2, but sometimes when I start entering a formula from scratch I just hit the "equals" sign. It feels like I'm editing a line of text but I'm actually in "enter" mode. Also, the little designation in the lower left has been there, roughly 30 inches from my eyeballs, for hundreds or possibly thousands of hours. It's changed state thousands if not millions of times. How have I only just now seen it? https://i.imgur.com/6yrULDU.png https://i.imgur.com/6yrULDU.png The poor programmer at Microsoft who invented the mode switching feature would be justifiably infuriated by the blindness of his users...
- sokoloff 4y ago> How have I only just now seen it? Haha. I'm in the same boat. When writing my comment, I opened Excel, clicked on a cell, and tapped F2 repeatedly, just to see if there was anything on the screen that changed...
- layer8 4y agoArrow keys: That’s actually consistent, F2 toggles the two modes (Enter and Edit), and the current mode is indicated in the status bar. See for example https://www.omnisecu.com/excel/worksheet/excel-cell-modes-ready-edit-enter-point.php https://www.omnisecu.com/excel/worksheet/excel-cell-modes-re.... It’s a bit like Vim modes.
- IIsi50MHz 4y ago> That's actually consistent, Not consistent with any program I know onWindows, Linux, macOS, or even ye olde Macintosh System Software, unless you count spreadsheet programs that are trying to be more like Excel. Not even consistent inside Excel, because there are many edit fields that default to "evil mode" (my personal feeling about "insert cell references when arrows keys are pressed, and disable Undo", while some default to "normal text editing mode", and some cannot be placed in "evil mode". I'd like a visual indicator on or adjacent to the text box, and setting to force it to default to one mode or the other.
- sokoloff 4y agoI use Option-Enter (on Mac, which is probably Alt-Enter on Windows) to insert line breaks in formulas: https://imgur.com/a/kXHzU9T https://imgur.com/a/kXHzU9T
- ketralnis 4y agoRight they explicitly mentioned it in the comment you replied to. They're asking for a regular normal human multiline text entry box that doesn't require a special mode where all of the keys mean different things than they're used to in code editors.
- sokoloff 4y agoDid they? > to do that I have to hold down [Alt] and mash the spacebar like a caveman.
- function_seven 4y agoI don't know why I wrote it that way. For the indenting, I have to use the space bar to line things up. No [Alt] needed in that scenario.
- function_seven 4y agoYeah, I do the same, but I wish I didn't have to. It's opposite of how I write text in all other contexts, and is really annoying when I forget to hold down [Alt] and get a modal admonishing me for having written a shit formula. Then I have to dismiss that modal, click on the cell again, and get back to where I was. Even worse, I spend a good chunk of time in Power BI, and it has a similar formula field (for DAX expressions), that mimics Excel a bit, but there you use [Shift] to insert newlines. So I'm always using the wrong modifier key and spewing insults at my computer.
- samwillis 4y ago> Please let me use [Tab] and [Enter] in the formula bar without trying to commit the change Exactly, there should be a contextual difference between editing in the cell directly or via the formula bar, when in the formula bar tab and enter should insert a tab or new line. Comment/Ctrl Enter (committing the change) or Escape (reverting the change) should be the only way to exit the formula bar via the keyboard.
- cm2187 4y agoThen how do you suggest you commit the change then (which is much more commn than adding tabs to a formula)? Hopefully not some combination of keys.
- TylerE 4y agoClocking into another cell?
- cm2187 4y agoDo you mean clicking? Using the mouse, really? Beside clicking on a cell already has a meaning, it inserts the address of the cell you are clicking on in the formula.
- function_seven 4y ago[Ctrl]+[Enter] would be awesome. Or just a double enter would work. Or clicking on the sheet somewhere. I don't want Excel to change the default behavior. The way it works now is the right way for most people. I just want to be able to enter a mode where I get to freely edit the formula as if it were in a text editor, then exit that mode when I'm satisfied. Whether that is some checkbox option buried in the settings ("Options > Formulas > Working with formulas"), or an F-key, I don't care.
- cm2187 4y agoCTR+ENTER is already taken. Select a range of cells, press F2, enter your formula, CTR+ENTER applies and fills that formula to the whole range (very useful).
- function_seven 4y agoOof. I actually use that frequently as well. Okay, okay, How about this? My wished-for option would just be to swap the behavior of [Enter] and [Alt]+[Enter]. Normally the first one commits the formula, the second one inserts a newline. I want to reverse that and make a naked [Enter] insert the newline, and the [Alt]+[Enter] commit the formula.
- pony_sheared 4y agoGive AFE a whirl, you can format and comment formulas https://www.microsoft.com/en-us/garage/profiles/advanced-formula-environment-a-microsoft-garage-project/ https://www.microsoft.com/en-us/garage/profiles/advanced-for...
- ec109685 4y agoInteresting that isn’t just a default feature in Excel.
- nerdponx 4y agoI want a pop-out formula editor window, and a menu along the side of the formula editor that lets me search for functions by name, and drag/drop them into the formula editor window.
- DwnVoteHoneyPot 4y agoIf you have multiple lines of formula, you should probably break up the formula across a few cells. This is similar to breaking up a long script into modules... each cell should have 1 purpose. Allows better testing of the formulas too.
- ilyt 4y agoThat's just ugly hack, formulas are essentially code and should be treated liek that
- user3939382 4y agoWe break up code into functions right?
- function_seven 4y agoI've used both VBA functions as well as the new LAMBDA() function, and they have their place. But there are legitimate reasons I don't want "magic" cells in my worksheet that exist only to be referred to by other cells. That kind of indirection comes with its own headaches. I try to make each cell useful for someone looking at that cell. Sometimes it makes sense to show the user the intermediate calculations. That's kind of the fundamental reason for spreadsheets—the paper kind!—in the first place. But I don't think it's right to spread a specific calculation out over many cells solely to avoid a complex function. Keeping it in the single cell—and using line breaks and indentation to make it more readable—is easier for me to maintain later, rather than bouncing around different locations in the sheet trying to reason about a given formula. Here's a real example where I'm listing the unique items from a data table that meet user-supplied threshold criteria: =UNIQUE( FILTER( data[Front Page Formatted], (data[Completed Month] = L$27) * (data[Expedite Rate in Month] >= cutoff_rate) * (data[Tickets in Month] >= cutoff_volume), "None" ) ) Those three filter criteria are booleans that are multiplied together. (Huh, should I have used AND() instead?) If all three are true, then the resulting list is UNIQUE'd and shown on the report page.
- layer8 4y agoYou can insert a tab character by pasting it. I think you could use AutoHotKey to detect when you're in the formula bar (the class of the active control is "EXCEL<1"), and then have it map Enter to Alt+Enter and Tab to pasting a tab character.
- function_seven 4y agoI assume you didn't see my other comment buried elsewhere in this thread, but that's exactly what I just did today. :) https://news.ycombinator.com/item?id=34178298 https://news.ycombinator.com/item?id=34178298 (Except I decided to go with 4 spaces from the tab key. I'll see what's up with pasting a literal tab. Maybe that's better)
- layer8 4y agoOh, right, I missed that. I generally prefer spaces, but tab should be possible. It might be worth a try to ControlSend a tab directly to the control; that could conceivably work without having to clobber the clipboard.
- LarryMullins 4y agoSounds great, but I hope they bring this feature to the local versions of Excel too. Using feature exclusivity to lure people into digital serfdom isn't cool.
- cratermoon 4y agoGreat. Can they fix the autocorrect so it doesn't mangle gene names? https://www.nature.com/articles/d41586-021-02211-4 https://www.nature.com/articles/d41586-021-02211-4
- sxg 4y agoExcel is a generic spreadsheet program that's designed to be used by 90% of people for 90% of use cases. Accommodating for this niche use case will definitely break things for many existing users. The real solution is for geneticists (and other data scientists) to use software designed more specifically for their use cases, such as CSV files and pandas or something more GUI-based but meant for researchers.
- tinus_hn 4y agoCSV files are unsuitable for any serious use and especially not a ‘better replacement for Excel files’. It’s an ill defined, locale dependent format of fail.
- sideshowb 4y ago"Not mangling stuff the user types in", yes, very niche requirement that, highly unlikely to benefit anyone but geneticists /s ;-)
- dan-robertson 4y agoI thought the real solution we got was to rename genes. Maybe only some were renamed.
- pessimizer 4y agoIs writing a headline as if it were promotional marketing copy just a reflex? I don't think that The Verge is owned by Microsoft (I could be wrong), but otherwise I don't get the tone. edit: maybe that tone is necessary to convince people that this is a story at all.
- cratermoon 4y agoAdvertorial content is essentially a press release written by the business and passed off to friendly, connected journalists. Sometimes they only cut it for length and don't even change the headline.
- deleted 4y ago[deleted]
- unsupp0rted 4y agoRecently I started building Excel addons with custom formulas (e.g. =company.inventory(upc, WAREHOUSE), which call an api. This has blown the minds of non-tech folks among my clients. They equate this to magic. I was forced into doing this because after a year of digging to find out what reporting they wanted in the dashboard (“oh a thousand things… where’s the Excel spreadsheet export button?”), I gave up and now default to giving them all reporting via Excel, using these custom formulas.
- rr808 4y agoAny new or good ways to do this? I used Excel-Dna 10 years ago.
- alar44 4y agoHonestly, GPT is pretty good at this type of stuff.
- unsupp0rted 4y agoGPT's code and docs for this is way out of date. I tried it a couple weeks ago.
- unsupp0rted 4y agoYup, javascript: https://learn.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-overview https://learn.microsoft.com/en-us/office/dev/add-ins/excel/c...
- legitster 4y agoSounds like a frustrating waste of time. Autocorrect/suggest is already one of least useful aspects of Excel. I would much rather they focus on improving the UI of writing formulas - perhaps a dedicated sidebar that let you blow up heavily nested equations?
- hermitcrab 4y agoWill it give us beauts like this?: =IFERROR(IF(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(SEARCH("Banner",AC5),SEARCH("EBL2",AC5)),SEARCH("Movie Art",AC5)),SEARCH("Use as is",AC5)),SEARCH("TTT",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1)<IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(SEARCH("Banner",AC5),SEARCH("EBL2",AC5)),SEARCH("Movie Art",AC5)),SEARCH("Use as is",AC5)),SEARCH("TTT",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1),MID(AC5,IFERROR(IFERROR(IFERROR(SEARCH("Customs",AC5),SEARCH("Custom",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1),IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(SEARCH("Banner",AC5),SEARCH("EBL2",AC5)),SEARCH("Movie Art",AC5)),SEARCH("Use as is",AC5)),SEARCH("TTT",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1)-IFERROR(IFERROR(IFERROR(SEARCH("Customs",AC5),SEARCH("Custom",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1)),MID(AC5,IFERROR(IFERROR(IFERROR(SEARCH("Customs",AC5),SEARCH("Custom",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1),IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(SEARCH("Banner",AC5),SEARCH("EBL2",AC5)),SEARCH("Movie Art",AC5)),SEARCH("Use as is",AC5)),SEARCH("TTT",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1)-IFERROR(IFERROR(IFERROR(SEARCH("Customs",AC5),SEARCH("Custom",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1))),"") (source: https://www.quora.com/What-is-the-longest-excel-formula-you-have-ever-created-for-one-cell https://www.quora.com/What-is-the-longest-excel-formula-you-... )
- meltyness 4y agoDon't even think about getting a CSE right the first time.
- jmkni 4y agoFor fun, I took this formula and asked ChatGPT to convert it to a VBA Function (with comments): Function extractText(cell As Range) As String ' Declare an array of search terms Dim searchTerms As Variant searchTerms = Array("Banner", "EBL2", "Movie Art", "Use as is", "TTT", "Generic") ' Initialize start and end positions to 0 Dim startPos As Long startPos = 0 Dim endPos As Long endPos = 0 ' Loop through search terms and find the first occurrence of any of them in the cell For Each searchTerm In searchTerms startPos = WorksheetFunction.Search(searchTerm, cell) If startPos > 0 Then Exit For End If Next searchTerm ' If none of the search terms were found, try finding "Customs" or "Custom" If startPos = 0 Then startPos = WorksheetFunction.Search("Customs", cell) If startPos = 0 Then startPos = WorksheetFunction.Search("Custom", cell) End If End If ' If none of the above were found, try finding "Generic" If startPos = 0 Then startPos = WorksheetFunction.Search("Generic", cell) End If ' If none of the search terms were found, return an empty string If startPos = 0 Then extractText = "" Else ' Loop through search terms and find the next occurrence of any of them after the start position For Each searchTerm In searchTerms endPos = WorksheetFunction.Search(searchTerm, cell, startPos + 1) If endPos > 0 Then Exit For End If Next searchTerm ' If none of the search terms were found after the start position, set the end position to the end of the cell If endPos = 0 Then endPos = Len(cell) + 1 End If ' Extract the text between the start and end positions extractText = Mid(cell, startPos, endPos - startPos) End If End Function Edit: Sadly this doesn't work at all lol, and after half an hour of prompting ChatGPT can't figure out why, it just gets stuck in a loop :-(
- cm2187 4y agoYou can't stop clippy!
- fisherjeff 4y agoOkay but when are they fixing Excel for macOS?
- blockwriter 4y agoTrying to reference the value of a cell in another sheet seems to work correctly maybe 50% of the time the first time I try it on Mac.
- cm2187 4y agoIf I had to ask for one killer new feature in Excel, it would be to adopt Apple's numbers approach to the design of a spreadsheet. I.e. not one grid per tab, but one canvas per tab, and that canvas can contain multiple elements which can be grids, charts, pivot tables, something else. Your grids can overflow with a scroll bar, so I can put one table above another one without them colliding when the top one expands. You can do that in a backward compatible way, if a canvas is not defined on an old spreadsheet, just assume one canvas that contains one grid set to full screen. It helps presentation, it helps splitting the logic of your spreadsheet in discrete components, I only see upside.
- tanin 4y agoWell, shameless plug. https://superintendent.app https://superintendent.app (paid with free trial) enables you to load a bunch of CSVs and write SQL on those CSV files. It's a much faster to work with if you know SQL well. It can also handle millions of rows easily (e.g. Loading 1GB CSV file takes 10s on Macbook Pro). Excel can't load a CSV larger than 1M rows. I initially built it because I had to identify the mismatched transactions between 2 giant CSVs using. Using "full outer join" with Superintendent.app took only a minute to do.
- capableweb 4y agoYou are eventually gonna have to write comments about other things than just plugging your own projects, especially when they are just barely on topic.
- NotACop182 4y agoHe is shameless. I second this comment.
- crazygringo 4y agoAs noted, Google Sheets introduced this over a year ago [1]. But this is exactly what competition is about -- I love seeing this come to Excel precisely as an answer to Google's version. You have to wonder if Microsoft would have tried it otherwise, since Excel is so entrenched there's less profit motivation for innovating. Sometimes it feels like "office" software hasn't changed much since the 90's, but when you look at cloud, collaboration, and machine learning, it's still constantly reinventing itself even if the interface still looks largely the same. [1] https://www.theverge.com/2021/8/26/22642192/google-sheets-intelligent-formula-functions-spreadsheet-context-aware-math https://www.theverge.com/2021/8/26/22642192/google-sheets-in...
- twobitshifter 4y agoAgreed, most of what people produce are facsimiles of typewritten documents, account ledgers, and overhead slides.
- dan-robertson 4y agoDo google also have the programming-by-example feature for suggesting formulas that’s in excel?
- logicallee 4y agoFor anyone who writes Excel formulas, it is one of the use cases for ChatGPT (a currently free chatbot that appears highly intelligent in some contexts) that users rave the most about. The next time you're writing an excel formula, give chatgpt a try. It might just get it right the first time.
- SoftTalker 4y agoOr it might get it wrong, but not obviously so. Who's job is it to debug/prove the ChatGPT-authored formulas that business people will certainly start using (if they haven't already). I have enough trouble debugging code I write myself. Debugging other people's code is much harder. When a company makes a multi-million dollar error because an analyst used ChatGPT formulas in Excel, I expect we'll get the answer.
- gadtfly 4y agoThis almost certainly is GPT. OpenAI has an exclusive licensing agreement with Microsoft.
- dan-robertson 4y agoWhy do you think it isn’t due to the programming by example work that MSR have been doing for over 5 years and which already partly made it into excel as flash fill?
- gadtfly 4y agoYou're right, "almost certainly" is far too strong. I was unaware of that research, and read a lot into that this is only being released in the web version. And am just generally anticipating a wave of LLM applications to eat everything that could be even remotely, abstractly reframed as an "autocomplete" task. That it only works in English is also evidence against, with GPT-3 you'd get every language it knows pretty much for free.
- pseudosavant 4y agoOne of the best ways Microsoft has made Excel formulas better is by allowing line breaks (using alt-enter) and tab/spaces alignment. So instead of seeing something like this: =IIF(foobar, pv(a1:a100), IFERROR(fv(b1:b100), 0)); You can do this using alt-enter: =IIF( foobar, pv(a1:a100), IFERROR( fv(b1:b100), 0 ) );
- drexlspivey 4y agoMicrosoft is changing the meta again just before BallmerCon. They should drop huge patches like this after the biggest tournament of the year..
- yyyk 4y agoNote Excel eSports actually exists: https://www.fmworldcup.com/ https://www.fmworldcup.com/
- shockeychap 4y agoMy #1 wish for Excel is named aliases in a formula so that I don't end up with repetitive incomprehensible formulas that are impossible to follow because I'm using the same VLOOKUP in three different places for a calculation. My #2 wish is a way for it to automatically break down and indent nested function calls for readability. The color coded brackets help, but only a little.
- QuantumGood 4y agoLike this? https://support.microsoft.com/en-us/office/define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64 https://support.microsoft.com/en-us/office/define-and-use-na...
- rahimnathwani 4y agoFor #1 you mean like LET? https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999 https://support.microsoft.com/en-us/office/let-function-3484...
- xupybd 4y agoI want better formula formatting and version control of my formula changes.
- KMag 4y agoQuick shout out to Ellx[0] (I presume, Excel pronounced backwards), which I discovered on HN a couple of years ago when discussing where I'd like to see spreadsheets evolve. It's basically functional reactive spreadsheets in JavaScript, with a storage format that's friendly toward code review, source control, diffs, etc. It automatically performs function lifting, so functions over single values become time-varying functions when applied to time series inputs. It's pretty impressive, especially since my impression is that it's mostly one person's hobby project. [0] http://ellx.io http://ellx.io
- twobitshifter 4y agoSeems like something I’d really like, but when I went looking at the demos and docs it was spitting lots of errors on Safari Example: https://ellx.io/ellx-hub/lib https://ellx.io/ellx-hub/lib
- KMag 4y agoNot my project, but I'll ping it over to Dmitry. One thing is that it's possible that Ellx uses exceptions to detect time series and lift regular functions into time series functions. I'm not sure the implementation details. There are all kinds of ways exceptions can be abused to implement near-magic. (I believe there's a library that uses exceptions in OCaml to implement coroutines.) I wouldn't be surprised if Ellx used some try-catch near-magic that doesn't quite work on Safari.
- dmaevsky 4y agoCreator or Ellx here. The reason is quite prosaic: I haven't maintained the site for almost a year :/ For various reasons. However, I'm getting back at it now. Ellx as a framework has evolved during this period, and is capable of much more than just a spreadsheet now, but this progress hasn't made it to ellx.io just yet. May I ask what you liked most about Ellx? What is your use case?
- twobitshifter 4y agoMy use case is a tool that produces living documents mixing writing with charts, data, and tabular results that can be delivered to nontechnical clients and updated and modified by staff with excel knowledge. I’ve found various JS notebooks like starboard and observable JS to be the closest thing, but they’re really not there. PowerBI is a Microsoft solution but it’s dashboard focused.
- RajBhai 4y agoCan they just not turn the active document into the file I just exported the workbook as?
- kurthr 4y agoOne aspect that Excel almost seems to enforce for any significantly complex computation is... testing. If you don't known have test input (e.g. an array of values) for which you have known output to observe for such an opaque programming language, you will be bitten! This innovation just makes it even more important. That said Excel lets non-programmers create visual representations of data on a regular basis, and everyone has it on their desktop so there's no, "I can't use this" excuse.
- Tagbert 4y agoI tend to deal with that by using intermediate calculations. I can verify subsets of the formula and then use the results of those subsets in the next stage of the overall formula
- Iwan-Zotow 4y agoGods of Microsoft, please (PLEASE!) add Python as Excel scripting language
- wombatpm 4y agoWhile I would love that, I much prefer a gut compatible diff for excel files. I’m so tired of files like budget-final-v2-final-final-approved.xlsx
- phonon 4y agohttps://www.xltrail.com/integrations https://www.xltrail.com/integrations
- IIsi50MHz 4y agoPyXLL, openpyxl, xlwings ? I think last time I looked (~3 years ago) PyXLL seemed most advanced, useuful, integrated, least buggy, but requires subscription after 30 days.
- bernardv 4y agoExcel is still a killer app. Microsoft has undermined it for decades. it’s a shame.
- magicalhippo 4y agoMy absolute, by far, #1 wish is to be able to use English function names regardless of the display language used. It drives me up the wall each time I have to use Excel and hunt for the right function name. And I'm a native Norwegian!
- w0de0 4y agoformulae :(