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
24 comments
-
Mark
commented
Would very much like something like LambdaPlayground which I understand is internal to Microsoft currently. See: https://youtu.be/tfz4jdwsEaQ?t=262
-
Anonymous
commented
Let's be honest, this really has to replace VBA if the online thing is going to take off. With that in mind, you need a dockable formula bar, indenting, watch window equivalent that incorporates the evaluation. A mini-programming interface if you like.
However!!!! Users need to be able to turn it back to classic mode. Many of the changes are designed around the advanced user. A lot of people just want to VLOOKUP their SUM functions if you get me.
-
Malcolm
commented
The whole process of adding LAMBDA functions into name manager without any checking is madness. LAMBDA functions should be able to be typed into something with all the checking and editing facilities of the formula bar.
It surely should be possible to repurpose most of the current formula bar code allowing the entry of a LAMBDA function into something similar to a cell and providing exactly the same experience of entering a formula into a cell?
When editing a formula which contains a LAMBDA function there should be a one-click method of getting to the LAMBDA function editor.
-
Anonymous
commented
See below for suggestions on real function help and a re-classification model.
General Comment:
Microsoft needs to further change in terms of viewing help on functions from a user's perspective. It has made enormous efforts in this direction since Ron De Bruin and I started to address this need. But there is still a long way to go and they really need to study ordinary users and what problems they have. That would make a product that would market itself.
What we've already done:
Too long ago, Ron De Bruin (MVP) and I (Ex MVP) set up a purpose led re-classification of all excel functions. We also linked this classification to 468 separate files that gave user orientated help on all functions.A copy of the VBA excelfunctionbible can be uploaded from Ron’s site. I’ve tried to upload a copy of the menu and a sample (NETWORKDAYS.INTL but it just hangs up at the horror of checking out the VBA. Any contact to my well-publicised email can request a copy of the original VBA menu and a sample.
Individual Users Orientated Help:
The VBA-free version requires a significant re-structuring and re-organization of the content but Ron and I have agreed the basic form to be adopted. We are at present significantly affected by Ron's difficulties in Covid19 Netherlands. I should add that I have health issues too.
Each function's Help comprises
Source: (Date introduced)
Description. In plain English
Syntax (Highlighting, required and optional arguments)
Related to or Similar to list of functions
Error Messages and what that it means user has done wrong
Proof of validity of function and known errors / inconsistencies
Basic Examples
Cross-checked calculations from base formulas and other functions.
Data Validated Examples
Mini-applications.
Essential Concepts UsedRe-structured Classification of Functions:
We perceived that users' main difficulty with the larger existing classifications was not being able to see the wood for all the trees. Our revised classification adopts the existing classification but introduces sub-classifications according to purpose:
Array Returning Functions
Compatibility - All previously statistical functions replaced by new improved ones.
Cube - No change
Database - No change
Date & Time
Building Dates Or Times
Components Of Dates Or Times
Date And Time Calculations
System Date
Deceased
Engineering
Bessel
Bitwise Number Operations
Complex Numbers
Conversion of Measurements
Error
Number Bases Conversions
Testing
Financial
Amortization
Conversions
Discounted Cash Flow
Depreciation
Securities
Information - No change
Logical - No Change
Lookup & Reference
Maths
Combinations
Information / Equivalents / Operations
Factorials
Matrix
Multiple Returns
Random Numbers
Rounding
Summing
Trigonometry
Statistical
Averages
Count & Permutations
Deviations & Variance
Line
Ranking
Testing
Text
Cleaning
Conversion
Finding and Extracting
Information
User Defined - See note below
Web - No ChangeWe published as the original copyright free excelfunctionbible but that relied upon VBA and that limited utility.
We are currently working on a VBA free version using the extended capabilities of Excel and especially the use of The Cloud as the home of the separate files for each of the 490 functions covered.
Novel User Defined Functions Idea:
One novel idea has been to free up the User Defined category by transferring its sole occupant EUROCONVERT (as of historic interest only) to a redefined "Deceased" Classification of functions which were killed off.
That means that User Defined can be used for 'approved' new functions such as:
EASTER (Western and Easter flavours) which determine 22 different and moving Public Holidays and observances in Western Christian religions alone plus a similar number for the Eastern Christian religions. More to the point different faiths would be able to adapt the key dates for their own purposes.
The more easily used suite of 10 interest conversion functions to remove the horrific problems that users experience in manipulating nominal and effective interest rates. This was not assisted by official government definitions in US and UK being wrong. Such manipulations are crucial for rigorous understanding and application of the financial functions.
A conceptually possible function for calculating sunrise and sunset based upon inputs of longitude and latitude. The main difficulty here is adjustment for 'local time'.
No doubt there are many other possibilities.
Norman Harker
-
Anonymous
commented
See below for suggestions on real function help and a re-classification model.
General Comment:
Microsoft needs to further change in terms of viewing help on functions from a user's perspective. It has made enormous efforts in this direction since Ron De Bruin and I started to address this need. But there is still a long way to go and they really need to study ordinary users and what problems they have. That would make a product that would market itself.
What we've already done:
Too long ago, Ron De Bruin (MVP) and I (Ex MVP) set up a purpose led re-classification of all excel functions. We also linked this classification to 468 separate files that gave user orientated help on all functions.I attach an original copy of the VBA excelfunctionbible 'menu' and a copy of the NETWORKDAYS.INTL as a sample the menu called up
Individual Users Orientated Help:
The VBA-free version requires a significant re-structuring and re-organization of the content but Ron and I have agreed the basic form to be adopted. We are at present significantly affected by Ron's difficulties in Covid19 Netherlands. I should add that I have health issues too.
Each function's Help comprises
Source: (Date introduced)
Description. In plain English
Syntax (Highlighting, required and optional arguments)
Related to or Similar to list of functions
Error Messages and what that it means user has done wrong
Proof of validity of function and known errors / inconsistencies
Basic Examples
Cross-checked calculations from base formulas and other functions.
Data Validated Examples
Mini-applications.
Essential Concepts UsedRe-structured Classification of Functions:
We perceived that users' main difficulty with the larger existing classifications was not being able to see the wood for all the trees. Our revised classification adopts the existing classification but introduces sub-classifications according to purpose:
Array Returning Functions
Compatibility - All previously statistical functions replaced by new improved ones.
Cube - No change
Database - No change
Date & Time
Building Dates Or Times
Components Of Dates Or Times
Date And Time Calculations
System Date
Deceased
Engineering
Bessel
Bitwise Number Operations
Complex Numbers
Conversion of Measurements
Error
Number Bases Conversions
Testing
Financial
Amortization
Conversions
Discounted Cash Flow
Depreciation
Securities
Information - No change
Logical - No Change
Lookup & Reference
Maths
Combinations
Information / Equivalents / Operations
Factorials
Matrix
Multiple Returns
Random Numbers
Rounding
Summing
Trigonometry
Statistical
Averages
Count & Permutations
Deviations & Variance
Line
Ranking
Testing
Text
Cleaning
Conversion
Finding and Extracting
Information
User Defined - See note below
Web - No ChangeWe published as the original copyright free excelfunctionbible but that relied upon VBA and that limited utility.
We are currently working on a VBA free version using the extended capabilities of Excel and especially the use of The Cloud as the home of the separate files for each of the 490 functions covered.
Novel User Defined Functions Idea:
One novel idea has been to free up the User Defined category by transferring its sole occupant EUROCONVERT (as of historic interest only) to a redefined "Deceased" Classification of functions which were killed off.
That means that User Defined can be used for 'approved' new functions such as:
EASTER (Western and Easter flavours) which determine 22 different and moving Public Holidays and observances in Western Christian religions alone plus a similar number for the Eastern Christian religions. More to the point different faiths would be able to adapt the key dates for their own purposes.
The more easily used suite of 10 interest conversion functions to remove the horrific problems that users experience in manipulating nominal and effective interest rates. This was not assisted by official government definitions in US and UK being wrong. Such manipulations are crucial for rigorous understanding and application of the financial functions.
A conceptually possible function for calculating sunrise and sunset based upon inputs of longitude and latitude. The main difficulty here is adjustment for 'local time'.
No doubt there are many other possibilities.
Norman Harker
-
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.