Hacker News Reader

Comment by function_seven | Hacker News Reader

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.

View on HN

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.

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.

@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

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.

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

The poor programmer at Microsoft who invented the mode switching feature would be justifiably infuriated by the blindness of his users...

> 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...

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-re.... It’s a bit like Vim modes.

> 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.

I use Option-Enter (on Mac, which is probably Alt-Enter on Windows) to insert line breaks in formulas:

https://imgur.com/a/kXHzU9T

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.

Did they?

> to do that I have to hold down [Alt] and mash the spacebar like a caveman.

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.

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.

Give AFE a whirl, you can format and comment formulas

https://www.microsoft.com/en-us/garage/profiles/advanced-for...

Interesting that isn’t just a default feature in Excel.

> 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.

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.

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

(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)

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.

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.

That's just ugly hack, formulas are essentially code and should be treated liek that

We break up code into functions right?

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.

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.

Clocking into another cell?

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.

[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.

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).

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.

I think shift+enter for new line would be a better choice if you were to highjack an existing shortcut. Probably barely used by anyone and it would be consistent with a line break shortcut in word/powerpoint and elsewhere.

Committing a formula needs to be a simple shortcut because it has to be used by the least technical users. You don't want to create a "how to exit VIM" mess in a retail product.

You're right, I don't want to change defaults. In fact, this whole back and forth reminded me that AutoHotKey exists, and I'm actually running it already, and I have the power to make this change myself. So I did!

    #if IsExcelFormulaBox()                ; Whenever the formula edit box has focus
    Tab::Send {Space}{Space}{Space}{Space} ; insert four spaces when I hit [Tab]
    $!Enter::Send {Enter}                  ; commit the formula with [Alt]+[Enter]
    $Enter::Send !{Enter}                  ; insert a newline with bare [Enter]
    #if

does what I want, with the helper function:

    IsExcelFormulaBox() {
        ControlGetFocus, F, A
        return (F="EXCEL<1")
    }

Reply on news.ycombinator.com