Add real multiline editing with auto-indentation to the formula editor
Formulas today are increasingly complex especially since the character limit has been increased. It's common to see many nested functions that are difficult to read unless pasted into Notepad and separated by line breaks with logical indentation. Why not have the formula editor incorporate multiline display with automatic indentation like other code editors?
Nick Boddington commented
What would be great is to combine the Evaluate Formula dialogue box with something like this : http://excelformulabeautifier.com/
David Nightingale commented
this feature gets my vote, and would be great to have /*comments and documentation*/ too
Michael Rudmin commented
This should be combined with suggestion 17940553. They are the same. That has eight votes, this has 14. And yes, I agree. But it isn't quite as simple as that. What they are asking for is a better formatting. If(cond,true,false) doesn't necessarily nest well, but there are ways it could be better. Maybe something like (with CondTrue an added green grey mininote, Condfalse an added red grey mininote like the mini notes here 'flag idea as inappropriate')
if(cond., (tab ++) (CondTrue) vlookup (concatenate(item 1,item 2),A$1:F$4,2,false),
(tab ++) (CondFalse) 0)
shows one level of nesting; two levels of nesting would be:
if(cond., (CondTrue) vlookup (concatenate(item 1,item 2),
I'd argue that a little click box in the corner of the formula bar could flip it to some better format, for easy viewing, on a once-off basis. Alternatively, hovering over the formula bar could pop up an instruction like "press F12 to increase nesting, F11 to decrease nesting" until people were used to it.
Add the ability for notes to be entered as well. This should function like the DAX formulas in Power BI Desktop with comments, numbered rows, and the ability to set a variable.
Vaibhav Garg commented
FormulaDesk, at least going by the screenshots on the site, seems to fit the requirements. If only this could be (a) native, and (b) allowed editing the formulas in an environment close to most IDEs such as Visual studio; would be wonderful.
John Loach commented
And the ability to include comments directly in the Formula (without resorting to hacks e.g. =N("") would also be very useful
John Loach commented
And the ability to include comments in the Formula (without resorting to hacks e.g. =N("") would also be very useful
Yeah, this would be very useful! At least something similar like in Power BI Desktop.
Will Demere commented
It can be difficult to create and modify formulas with multiple nested functions, particularly when the functions are typed directly into a cell or the formula bar. With the formula bar, all functions continue on the same line and it can be difficult to keep track of the nesting structure, even with the colored sets of parentheses and function tooltips. It would be helpful to have a “nested” formula bar appear when a particular nested function is selected. The selected function could be displayed separately in a pop-out “nested” formula bar, which would make it much easier to ensure that all arguments are completed and to keep track of the nesting structure. Another option would be to have a hierarchical structure of multiple cascading formula bars that would provide a much clearer picture of the relationship between the nested functions.
Stephanie Hawley commented
It would be nice if you could drag n' drop sections of code around too, but I'm not sure how that would work.
I hate it when I get lost and have to copy it over to some thing like notepad++
Kenneth Barber commented
Automatic indentation should be an option alongside the current "no indentation".
Gareth Hayter commented
Does something like the 'Formula Explorer' in FormulaDesk help, even though it's not editable, but helps with understanding complex formulas? Or do you need more help with actual editing, not just understanding? http://www.formuladesk.com