Formula editing improvements Part 2: Formula AutoComplete

re: Formula editing improvements Part 2: Formula AutoComplete

Jon Peltier — Thu, 20 Oct 2005 01:53:20 GMT

Intellisense in the worksheet. Awesome.

re: Formula editing improvements Part 2: Formula AutoComplete

Jordan Windebank — Thu, 20 Oct 2005 01:53:27 GMT

I get the impression that this is sort of like how Visual Studio works with autocomplete of syntax? I like this very much.

Looking forward to Excel 12 more and more daily.

Cheers,
Jordan

re: Formula editing improvements Part 2: Formula AutoComplete

Graham — Thu, 20 Oct 2005 03:07:53 GMT

So... please help me understand why you're using a function number rather than just the name of a function. When we're skimming through a sheet, or looking at a print out of formula view, we'll have to have learned which numbers refer to which functions, won't we? Or am I missing the point completely..?

G

re: Formula editing improvements Part 2: Formula AutoComplete

Rob van Gelder — Thu, 20 Oct 2005 03:13:40 GMT

David,

Nice work on autocomplete!

I should have asked in my previous post:
Will there be a 'beautifier' for formulas?
That is, a feature which formats/tabs/indents formulas for easier reading -like the AudXL addin on my website (screenshots incl.)?

Rob

re: Formula editing improvements Part 2: Formula AutoComplete

Step — Thu, 20 Oct 2005 03:30:00 GMT

Beautiful ... (wipes tear from eye). I can't wait to start using Office 12!

re: Formula editing improvements Part 2: Formula AutoComplete

Biff — Thu, 20 Oct 2005 04:21:15 GMT

"Excel functions – this includes all functions in the Excel library "

Does that include functions from the ATP?

Are automatic tooltips avaiable for ATP functions in this version?

Is this drop down moveable?

re: Formula editing improvements Part 2: Formula AutoComplete

David Gainer — Thu, 20 Oct 2005 07:56:35 GMT

Howdy

Graham, perhaps I wasn’t clear. In the example, the number is an argument to the SUBTOTAL function that specifies what sort of subtotal to create – in the case of our argument, it specifics we want a SUM. That isn’t a change from previous versions of Excel – it is the authoring UI that has been improved, so that instead of having to look up the arguments for SUBTOTAL in help every time you use the function, you can just pick from a list. If you were using a function that doesn’t have enumerated arguments, like SUM, you wouldn’t see the numbers. When you look at formulas in the worksheet, they will be the same as in Excel 2003.

Jordan, yes, you are correct.

Rob, no, we haven’t done work in that specific area this time out.

Biff, yes this includes the ATP (more on that tomorrow). No, the drop down is not movable, but I think when you use it, you will find that it is not obtrusive.

re: Formula editing improvements Part 2: Formula AutoComplete

Harlan Grove — Thu, 20 Oct 2005 08:25:10 GMT

For the curmudgeons among us, will there be any way to disable this functionality like there is in the VB Editor (Options dialog, Editor tab, uncheck Auto Quick Info)?

re: Formula editing improvements Part 2: Formula AutoComplete

Graham — Thu, 20 Oct 2005 09:33:50 GMT

Thanks David - now I understand - I wasn't familiar with the subtotal function from Excel 2003. My mistake, thanks for the clarification.

G

re: Formula editing improvements Part 2: Formula AutoComplete

DavidL — Thu, 20 Oct 2005 09:54:02 GMT

This sounds great! However, if you want to achieve goal 2, not relying on external help for formulas as much, wouldn't it be good if AutoComplete also listed suggestions for internal functions that had the text typed in _part of_ its name? That is, if I know that I want to insert some kind of total function, I can start writing "TO" and see "SUBTOTAL" in the AutoComplete list. Then, if I type something that doesn't even match anything, like "STANDARD", the list could expand to a fulltext search of the online help and suggest "STDEV".
I know, this isn't how VS IntelliSense works, but this is Excel, free to innovate.
On the same note, I wish that Excel could be more helpful when I made an error in the formula. For example, if I use "STDEV" in Swedish Excel, it will simply display a "#NAME?" error ("#NAMN?", actually), but I will have to search the help myself to find that I really should write "STDAV", because the Swedish word for "deviation" is "avvikelse". For localized versions of Excel, it would be nice if it tried to match unknown function names with the English built-in functions and suggest the localized counterpart, if there is one. Also, other "soft" methods here, like trying to match part of function names or search the online help, are probably better than what we have today.

re: Formula editing improvements Part 2: Formula AutoComplete

Colin Walker — Thu, 20 Oct 2005 10:04:16 GMT

This just keeps getting better an better - Office 12 is going to be awesome. I just hope I get on the beta.

re: Formula editing improvements Part 2: Formula AutoComplete

TJM — Thu, 20 Oct 2005 10:06:14 GMT

Hi, the more I read about this the more I'm looking forward to the new version - just hope we have better luck convincing our IT departments to update than from XP to 2003!

1. The function number for SUM is different here (109) to that in Excel XP (9). What's the reasoning behind the change and are there conversion issues being introduced here?

2. Out of curiousity, the drop-down lists for the "ref1" parameter in your screen shot don't indicate you can type in / select a worksheet range. Which I would have thought was a common enough use of the function to merit showing up somewhere prominent on the drop-down? Do you not think less experienced users might treat the drop-down as an exclusive list of options?

re: Formula editing improvements Part 2: Formula AutoComplete

Charles Williams — Thu, 20 Oct 2005 10:28:44 GMT

Great stuff. I am eagerly looking forward to seeing what you have done in the revised Defined Name dialog.

Does anything happen if you right-click an entry in the Auto-complete drop-down?

re: Formula editing improvements Part 2: Formula AutoComplete

Paul Morriss — Thu, 20 Oct 2005 12:08:23 GMT

I like this, but can I turn it off? Most of the time I'm using a very limited range of functions and I know just what I want.

re: Formula editing improvements Part 2: Formula AutoComplete

Jan Karel Pieterse — Thu, 20 Oct 2005 13:07:02 GMT

Hi David,

Great enhancement to the formula editing indeed.

One remark/question about the autocomplete:

Now it filters by the characters entered, hiding the other entries. Why not do it the same as the VBE intellisense: just scroll to the matching one and keep the other in sight?

Regards,

Jan Karel Pieterse
Excel MVP

re: Formula editing improvements Part 2: Formula AutoComplete

Jean-Marc Decouleur — Thu, 20 Oct 2005 13:24:11 GMT

"User defined functions – Functions defined by the user either through VBA"

How will we be able to provide the comment that appears in the descriptive tooltip?

Also, is the AutoComplete feature available only when in-cell editing is done? What about editing done in the formula bar?

Thanks.

re: Formula editing improvements Part 2: Formula AutoComplete

Marcos Martins — Thu, 20 Oct 2005 14:04:39 GMT

Interesting.

re: Formula editing improvements Part 2: Formula AutoComplete

Jim Rech — Thu, 20 Oct 2005 14:21:48 GMT

TJM-

In Excel 2003 the Subtotal function was enhanced with additional function numbers 101-111. These match exactly the purpose of the existing function numbers 1-11 except they ignore manually hidden rows. So you know the older functions ignore rows hidden via a filter but not those manually hidden. I believe this enhancement was made because of the new List feature in Excel 2003.

re: Formula editing improvements Part 2: Formula AutoComplete

SteveA — Thu, 20 Oct 2005 15:38:56 GMT

Hi David
Feature looks good
How are you going to handle a function like Match, which has one argument match_type, which can takes values -1, 0, 1. I can never remember which value means what, and have to revert to help. Even current dialog box only lists the values and not what they mean.
OK so a minor point, but nice to improve if possible

re: Formula editing improvements Part 2: Formula AutoComplete

Colin Banfield — Thu, 20 Oct 2005 17:52:36 GMT

<<I should have asked in my previous post:
Will there be a 'beautifier' for formulas?
That is, a feature which formats/tabs/indents formulas for easier reading -like the AudXL addin on my website (screenshots incl.)? - Rob<<

<<Rob, no, we haven't done work in that specific area this time out.<<

A shame. For Excel 13 then, I have a request along the folling lines:

A dialog box like the one in Rob's addin would be useful for creating complex formulas, with features such as: 1) indenting, 2) parentheses matching (like entering a formula on the worksheet), 3) displaying the current cell reference value by hovering the cursor over a cell reference in the dialog box, 4) a button that displays the Evaluate Formula dialog box for testing the completed formula, 5) any other tools to help make building complex formulas easier.

Also, I wish there was a way to create UDFs in a dialog box within Excel, without having to invoke VBA functions. Two reasons for this: 1) Better calculation speed if you're using the function or functions in a lot cells on a large workbook, 2) For the average user, one shouldn't have to be familiar with VBA or switch to a VBA IDE to create a custom function (although the current functionality should still be available).

Colin

re: Formula editing improvements Part 2: Formula AutoComplete

Tianwei — Thu, 20 Oct 2005 21:08:13 GMT

Nice feature means to make formula entering very user-friendly. Why not go all the way to display the full help text for each argument as the argument is being entered? (and to a prior commentator's point, allow an option somewhere to disable the feature)

Perhaps a future enhancement note: when an argument is enters, have a different screen shows where the reference is actual at (for example, if a name is entered, the screen shows the blinking dotted line around the range the name refers to). Many times the names defined are very similar so it's easy to mis-enter one when there are a lot of names defined (pardon my lack of imagination when naming the names)

re: Formula editing improvements Part 2: Formula AutoComplete

simon murphy — Thu, 20 Oct 2005 22:33:44 GMT

Nice one David!
(F1 will take you to context sensitive help right?)
I take Grahams point about using the name rather than the function number - I'm guessing this is a backwards compatibility issue?)
cheers
Simon

re: Formula editing improvements Part 2: Formula AutoComplete

David Gainer — Fri, 21 Oct 2005 00:10:13 GMT

Greetings everyone.

Harlan, Paul – Yes, there is an option and there is also a keyboard shortcut to turn Formula AutoComplete off if you like. Paul, we believe that even if you know what you want, there are efficiency gains using this feature to insert functions rather than typing them out (fewer keystrokes needed).

DavidL – Thanks for the feedback. One place you can already search for functions is the Insert Function dialog. Also, I think Formula AutoComplete will actually help out in the situation that you describe in the second half of your question, as you will be able to see the function you are typing doesn’t exist before you finish.

TMJ – Formula AutoComplete does not autocomplete cell references, however you can type anything you want at any time. Like visual studio and VB in Excel, this is a feature to help you, but it doesn’t control what you type. Our testing to date indicates users will not feel limited to the options in the drop down; we will do further testing and be looking at beta feedback as we progress.

Charles – Nothing happens if you right-click.

Jan Karel – We felt that it was better to reduce complexity by filtering out items than just scrolling a large list. What would be your reasoning for wanting the VS behaviour (other than consistency)?

Jim – Thanks for the explanation of the additional function numbers.

SteveA – In addition to showing the numerical value, we will display a text string explaining them, much like the subtotal example above. That should help.

Colin, Tianwei – Great feedback, thanks.

Simon – I will take a look. Correct on the backwards compatibility point.

re: Formula editing improvements Part 2: Formula AutoComplete

Bob Whiton — Fri, 21 Oct 2005 05:49:31 GMT

Our product uses 'hidden' Defined Names. Will they surface in the auto complete? (Hope not!)

Awesome feature!

re: Formula editing improvements Part 2: Formula AutoComplete

Jean-Marc Decouleur — Fri, 21 Oct 2005 11:27:57 GMT

David - Seems you forgot my questions ? ;-)

"User defined functions – Functions defined by the user either through VBA"
How will we be able to provide the comment that appears in the descriptive tooltip?

Also, is the AutoComplete feature available only when in-cell editing is done? What about editing done in the formula bar?

Thank you.

re: Formula editing improvements Part 2: Formula AutoComplete

Jan Karel Pieterse — Fri, 21 Oct 2005 13:03:57 GMT

David:

"Jan Karel – We felt that it was better to reduce complexity by filtering out items than just scrolling a large list."

I get that, good point.

"What would be your reasoning for wanting the VS behaviour (other than consistency)? "

Well, just because one sometimes makes a typo (without actually looking at the autocomplete list). As it is now, you'd end up with an empty (disappeared?) list, in the other case, you'd probably be close to the entry you actually needed.

re: Formula editing improvements Part 2: Formula AutoComplete

David Gainer — Sat, 22 Oct 2005 02:01:03 GMT

Bob – No, hidden names do not surface in Name Autocomplete. I look forward to your feedback on my next post (name management improvements).

Jean-Marc – I am very sorry; not sure how I missed your questions the first time. Yes, Formula Autocomplete is also available when editing in the formula bar. Also, if you write UDFs using XLLs, we will display the function description as a tooltip. VBA and automation add-in UDFs will not have descriptive tooltips.

Jan Karel – thanks for the feedback.

re: Formula editing improvements Part 2: Formula AutoComplete

Matthew — Sun, 23 Oct 2005 19:19:33 GMT

David,
I can't help but smirk each time you roll out another fantastic new feature; this'll help no end.

Just one suggestion, some of the PHB's I work with could get seriously confused by formula autocomplete showing the argument lists of UDFs I've created in VBA, should they happen to type something with the same starting letters.

At the same time I wouldn't want the overhead of including VBA to switch formula autocomplete off & on upon open/close (in every file where this might apply)

Can we have an solution to prevent UDFs only from being included in formula autocomplete?

Thanks again.

re: Formula editing improvements Part 2: Formula AutoComplete

David Gainer — Wed, 26 Oct 2005 06:18:29 GMT

Hi Matthew - We only show the UDF function name in the Auto complete drop down list ... we don't auto complete any UDF arguments. Currently, we don't have an off switch for showing UDFs in F AutoComplete.

re: Formula editing improvements Part 2: Formula AutoComplete

nicolas — Fri, 28 Oct 2005 18:46:34 GMT

Sir,

You absolutely need to open this intellisense to allow user to complete with their own metamodel.

Say I defined a range called "mytimeserie" and I type it via some system as a Financial Time Serie. That class has properies like volatility, mean, etc..

I WANT to be able to type in "mytimeserie." and have completion. furthermore, I want to be able to attach specific helper, parameter validator, suggestion, for a user function, like "mytimeserie.volatility(" and to display a calendar for I know that is the type of the 1st parameter.

Exposing metamodel and providing standard extension point is absolutely crucial for ease of use.

Nicolas

CUBE Functions 3: Formula AutoComplete revisited

Microsoft Excel 12 — Fri, 10 Feb 2006 03:05:46 GMT

Previously when discussing CUBE functions, I showed a couple of examples of reports based on OLAP data...

Fun With Tables » Wagalulu - Microsoft » » Fun With Tables

Fun With Tables » Wagalulu - Microsoft » » Fun With Tables — Tue, 18 Jul 2006 16:47:07 GMT

PingBack from http://microsoft.wagalulu.com/2006/07/18/fun-with-tables/

Microsoft Excel Formula editing improvements Part 2 Formula AutoComplete | Outdoor Ceiling Fans

Sun, 31 May 2009 17:57:53 GMT

PingBack from http://outdoorceilingfansite.info/story.php?id=2404

Microsoft Excel Formula editing improvements Part 2 Formula AutoComplete | Outdoor Ceiling Fans

Sun, 31 May 2009 21:09:25 GMT

PingBack from http://outdoorceilingfansite.info/story.php?id=20040