5 ms·

My 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 for

My 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 ago
And [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 ago
    THANK 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 ago
    Ah, 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 ago
  Arrow 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 ago
I 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 ago
  Right 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 ago
    Did they? > to do that I have to hold down [Alt] and mash the spacebar like a caveman.
    - function_seven 4y ago
      I 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 ago
  Yeah, 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 ago
Then 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 ago
  Clocking into another cell?
  - cm2187 4y ago
    Do 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 ago
    CTR+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 ago
      Oof. 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 ago
Give 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 ago
  Interesting that isn’t just a default feature in Excel.
nerdponx 4y ago
I 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 ago
If 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 ago
  That's just ugly hack, formulas are essentially code and should be treated liek that
  - user3939382 4y ago
    We break up code into functions right?
    - function_seven 4y ago
      I'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 ago
You 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 ago
  I 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 ago
    Oh, 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.

LeifCarrotson 4y ago

And [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 ago
  THANK 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 ago
  Ah, 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 ago
Arrow 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 ago

I 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 ago
Right 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 ago
  Did they? > to do that I have to hold down [Alt] and mash the spacebar like a caveman.
  - function_seven 4y ago
    I 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 ago
Yeah, 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 ago

Then 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 ago
Clocking into another cell?
- cm2187 4y ago
  Do 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 ago
  CTR+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 ago
    Oof. 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 ago

Give 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 ago
Interesting that isn’t just a default feature in Excel.

nerdponx 4y ago

I 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 ago

If 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 ago
That's just ugly hack, formulas are essentially code and should be treated liek that
- user3939382 4y ago
  We break up code into functions right?
  - function_seven 4y ago
    I'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 ago

You 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 ago
I 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 ago
  Oh, 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.