New Formula editing experience
With the arrival of LAMBDA and LET we now need a new formula bar experience. Please vote and put your suggestions in the comments on what features you’d like the formula bar to have
Wyn Hopkins
shared this idea
19 comments
-
Charles Williams
commented
There are several good addins for exploring/debugging formulas (FastExcel Explorer Pro, Arixcel ...). But making a good editor as an addin is very difficult so I absolutely agree that improvement is needed, particularly for LAMBDA. Some ideas include:
Indent on/off and or sub-expression treeview
Smart Indent understands IF etc
Color code the result path from IF, IFS, CHOOSE etc
Color the syntax and formula precedents
Double-Click an offsheet reference does a GoTo
Built-in Evaluate option that handles scalars, arrays, ranges, functions including Lambdas and Lets, with ability to filter array results
Debug option that handles recursion
Option to expand/contract defined names, LETs and LAMBDAS into their definitions
Formula comments
Function argument intellisense and Help
Intellisense for Lanbdas and UDFs
Forward and backward step-by-step debug
Integrated precedents and dependents
... -
Ahmed gamal
commented
we need a formula which transfer numbers to words its important when we doing jv or printing checks I can do this formula by using VB but its better if the workbook excel included ,
-
Andrew Moss
commented
For starters, we need a pop-out formula bar so us with dual screens can write long formulas on one and have access to our workbook on the other.
We also need the ability to be able to write comments in our formulas.
-
Mourad
commented
New Formula with intellisense with color and evaluating option
-
Michael D. Newby
commented
I agree but also why stop there? There are so many things that need addressed. We keep reading about new features but they never address many user comments and issues...especially MVP input. Sorry, but Microsoft hears but hasn't learned to listen.
-
Jon Peltier
commented
I imagine we'll need more than 8192 characters in a formula soon, especially if we start to allow inline comments and indenting.
-
excel1star
commented
The new-look ahead to select a function or a range entry that is triggered by entering “=” is great!
Using the tab key to make complete the selection is intuitive.
But once the function is selected you need to use CRTL+Shift+A to add the argument list
Way not just a second Tab!
On furthermore, there should be a similar resource for VBA. When a function is entered in VBA the information about its arguments are too cryptic. The arguments should be listed just like being entereed on the spreadsheet.. -
Anonymous
commented
It would be nice to have wiggly red underlines on parts of the formula that excel can't understand, like we get in DAX when typing formulas into Power Pivot.
Edit: Oh, also, please could we have the functionality of "Evaluate Formula" built into the formula bar somehow? That would be quite useful. Imagine if you've just finished typing part of a formula, you can select what you've just typed, see what it evaluates to, and then continue working on the rest of the formula.
-
Diederik
commented
I would love to see a Power BI Desktop DAX formatting type of look and feel
-
Branislav
commented
- Line numbers,
- Color coding,
- Auto indent when Shift+Enter,I quite like Power BI Desktop formula bar experience, and while not perfect, perhaps a step in a right direction for Excel too.
-
Brad Yundt
commented
When developing a named formula, LAMBDA or LET, there needs to be a better experience for debugging. Improved error messages would help. Ideally, we would be able to enter some test values and evaluate the formula in steps. Right now, the LAMBDA has to be perfect before it is accepted, so it can be difficult to see where the problem lies.
-
Soumalya Biswas
commented
We need something like the intellisense and features from a code editor, as slowly Excel will have powerful programming capabilities.
-
David Gerard
commented
I do agree with John MacDougall that something similar to the experience of the formula bar in Power Apps would be awesome.
-
Patrick O'Beirne
commented
Same as existing Formula Analyzer addins (sorry Charles & Jan) but allow editing. Features: indentation, collapse/expand sub expressions, integrate with Formula Evaluation to allow debugging.
-
Celia Alves
commented
Auto-expand when the formula is written through multiple rows, and auto-reduce when the number of rows used in the formula bar also reduces.
-
Anonymous
commented
There needs to be some sort of ability to indent what has become, with the advent of LAMBDA, actual code in order to make it readable. PowerApps allows for auto format once you’ve written your formula but actually allowing the formula in excel to be written with white space and indentation would be brilliant.
-
Brent Allen
commented
As formulas in Excel get more complex, there needs to be some way to auto-indent.
It's a great feature in DAX studio, and will be critical as formulas become more like VBA UDFs.
-
John MacDougall
commented
I really like the Power Apps formula bar, it's not perfect but it is hundreds of times better than Excel's formula bar.
- There's a button to auto format or auto remove format
- Everything's colour coded
- Errors are underlined
- Allows single line comments // and multi-line comments /**/
- There's an evaluation preview when you select something (shows scalars, arrays and table results and tells you what the data type is)I want those
-
Wyn Hopkins
commented
- expands and indents with Shift Enter
- allow comments
- can be detached and floated
- formula can be named ( for lambdas)
- allow auto expanding of formula bar to be turned off and on easily
- colour coding when referencing variables
-