3 ms·

Ok, besides python support... The thing I'd like more than anything: A better way to edit cell with really long function calls. If nothing else, add color cod

Ok, besides python support... The thing I'd like more than anything: A better way to edit cell with really long function calls. If nothing else, add color coded parenthesis to the bar at the top and not just in the cell. Like, sometimes you just need some if/else statements... but try to parse and edit even something fairly simple like: =IF(AND(LongExpression > 3, Other_longexpression<5),AnotherLongExpression, IF(AND(LongExpression>5,Other_longexpression<10), AnotherLongExpression, 0)) Even with the color-coded parentheses, this is really hard! And God Forbid all those "LongExpression" have a bunch of parenthesis and PEMDAS that needs to be respected. It's... really goddamn tedious. I lost track of the parenthesis while writing that in this window ...However, if I could just have a little popout window where I could add arbitrary new/lines and spaces, that would make a difficult thing into something downright enjoyable and productive. Something like: =IF( AND( LongExpression > 3, Other_longexpression<5 ), AnotherLongExpression, IF( AND( LongExpression>5, Other_longexpression<10 ), AnotherLongExpression, 0 ) ) Would make things much easier to parse.

collegeburner 4y ago
+1 for python support. getting vba versions right for stuff that expects to interface through it can be a bitch if some vendor doesn't update stuff. you pay some stupid amount for a financial data product then it's like "lol this doesn't work with your version of office install 2016". not really excel dev's but python would be nice.
RodgerTheGreat 4y ago
FYI, the formula bar in Excel is resizable already. Expand the bar (or drag the vertical resize handle at its bottom edge), and then you can use alt+enter to insert a newline.
- ant6n 4y ago
  It's like coding in an adversarial text window -- the font isn't monospace, there's no syntax highlighting, no auto-indent based on parenthesis, and when you accidentally hit enter (instead of ctrl+enter) you leave the text editor (and then it'll pop up with complaints about your function not being parseable). Also, dragging the size of the edit box bigger means you won't get much of a view of your spreadsheet -- it would probably be nice to attach the edit window on the right (if it was turned into a proper formula editor).
elefanten 4y ago
+1 for color coding in top formula bar
Swenrekcah 4y ago
Great suggestion, but don’t do it Microsoft. This is one of my signals for when to move on to another tool so if you implement this I’d stay in Excel longer!
inglor 4y ago
> If nothing else, add color coded parenthesis to the bar at the top and not just in the cell. This seems like a simple enough suggestion - I will pass on the rest but let me see if this is something we can fit to the next FHL.
_dain_ 4y ago
Do you know about the LET function? It lets you make lexically scoped variables. And the IFS function is basically an if-elif(-elif...)-else chain, eliminating nested IF. So your example would be: =LET( foo, LongExpression, bar, OtherLongExpression, baz, AnotherLongExpression, bax, YetAnotherLongExpression IFS( AND(foo > 3, bar < 5), baz, AND(foo > 5, bar < 10), bax, TRUE, 0)) The formula bar can be resized and you can insert new lines with alt-enter. Sadly there's no easy way to indent, you just have to tap the spacebar (or write the formulae in Notepad++ and copypaste across like I do). Also I recommend using Lisp style "close parentheses all at the end of the line" style, rather than "Egyptian brackets".
- Enginerrrd 4y ago
  Hey, this is all incredibly helpful! LET alone will be very helpful for all the empirical fluid mechanics formulas I have to deal with. Thanks a lot friend! Also, just out of curiosity, why do you recommend that particular bracket style?
  - _dain_ 4y ago
    Vertical space is at a premium, no sense in having lines with nothing but a single close paren. You don't want to drag down the formula bar and start hearing the Star Wars title crawl music in your head. It's worth checking out the big alphabetical list of formulae, there's a lot of things that many people don't know are there. Look at the ones with the "Office 365" or "2019" labels for recently added ones (although annoyingly those are icons rather than text so you can't ctrl-f): https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188 https://support.microsoft.com/en-us/office/excel-functions-a... Also check out my other comment in this thread about tables if you don't already know what those are all about. And Powerquery too.

collegeburner 4y ago

+1 for python support. getting vba versions right for stuff that expects to interface through it can be a bitch if some vendor doesn't update stuff. you pay some stupid amount for a financial data product then it's like "lol this doesn't work with your version of office install 2016". not really excel dev's but python would be nice.

RodgerTheGreat 4y ago

FYI, the formula bar in Excel is resizable already. Expand the bar (or drag the vertical resize handle at its bottom edge), and then you can use alt+enter to insert a newline.

ant6n 4y ago
It's like coding in an adversarial text window -- the font isn't monospace, there's no syntax highlighting, no auto-indent based on parenthesis, and when you accidentally hit enter (instead of ctrl+enter) you leave the text editor (and then it'll pop up with complaints about your function not being parseable). Also, dragging the size of the edit box bigger means you won't get much of a view of your spreadsheet -- it would probably be nice to attach the edit window on the right (if it was turned into a proper formula editor).

elefanten 4y ago

+1 for color coding in top formula bar

Swenrekcah 4y ago

Great suggestion, but don’t do it Microsoft. This is one of my signals for when to move on to another tool so if you implement this I’d stay in Excel longer!

inglor 4y ago

> If nothing else, add color coded parenthesis to the bar at the top and not just in the cell. This seems like a simple enough suggestion - I will pass on the rest but let me see if this is something we can fit to the next FHL.

_dain_ 4y ago

Do you know about the LET function? It lets you make lexically scoped variables. And the IFS function is basically an if-elif(-elif...)-else chain, eliminating nested IF. So your example would be: =LET( foo, LongExpression, bar, OtherLongExpression, baz, AnotherLongExpression, bax, YetAnotherLongExpression IFS( AND(foo > 3, bar < 5), baz, AND(foo > 5, bar < 10), bax, TRUE, 0)) The formula bar can be resized and you can insert new lines with alt-enter. Sadly there's no easy way to indent, you just have to tap the spacebar (or write the formulae in Notepad++ and copypaste across like I do). Also I recommend using Lisp style "close parentheses all at the end of the line" style, rather than "Egyptian brackets".

Enginerrrd 4y ago
Hey, this is all incredibly helpful! LET alone will be very helpful for all the empirical fluid mechanics formulas I have to deal with. Thanks a lot friend! Also, just out of curiosity, why do you recommend that particular bracket style?
- _dain_ 4y ago
  Vertical space is at a premium, no sense in having lines with nothing but a single close paren. You don't want to drag down the formula bar and start hearing the Star Wars title crawl music in your head. It's worth checking out the big alphabetical list of formulae, there's a lot of things that many people don't know are there. Look at the ones with the "Office 365" or "2019" labels for recently added ones (although annoyingly those are icons rather than text so you can't ctrl-f): https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188 https://support.microsoft.com/en-us/office/excel-functions-a... Also check out my other comment in this thread about tables if you don't already know what those are all about. And Powerquery too.