You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Last post I covered improvements we made to a long-time fixture of the Excel UI - the formula bar. Today I’d like to introduce a feature that is brand new for Excel 12. The feature is called Formula AutoComplete, and it is designed to make users more efficient. Specifically, when we designed Formula AutoComplete, we had three goals:
For guidance on the user experience we also looked to applications like Excel’s own VBE and Visual Studio, and we made sure to standardize where possible.
How it worksFormula AutoComplete consists of a drop down list of items matching the customer’s keyboard input. Each item type in the list has its own icon to visually distinguish it. (The icons you see in the pictures below are works in progress and not final).
This behaviour is probably best explained with an example. Let’s walk through the process of building the following formula with Formula AutoComplete:
=SUBTOTAL(109, data)
To start, I will do what I always do when creating a formula – type an “=” sign. For this example, I want to insert the SUBTOTAL function, so I begin with the letter “s”. Upon typing the letter “s”, the Formula AutoComplete drop down immediately appears and displays a list of options that start with the letter “s”. The first option in the list (in this case the SEARCH function) is selected and displays a description in a tooltip. At this point, I could insert this function into the cell by hitting the TAB key or double clicking on the item.
(Click to enlarge)
Instead, I keep typing. By the time I have typed “sub”, the list is filtered down to contain only the items starting with “sub” – SUBSTITUTE and SUBTOTAL. I can navigate down to the SUBTOTAL function by using the up and down ARROW keys. The descriptive tooltip confirms that this is the function I’m looking for.
To insert the SUBTOTAL function, I simply need to press the TAB. AutoComplete inserts the opening parenthesis in addition to the function name, and puts my cursor in the first argument of the function. In this case, the first argument (function_num) has a specific set of values to select from. AutoComplete makes it easy to choose one by displaying a list of the valid arguments when my cursor enters the argument. In this case, I want the SUM option (109), so I’ll select it and hit TAB to insert. Without auto-complete, I would have to either commit this list to memory or else consult help to get the correct value.
The final argument for the SUBTOTAL function is the reference to the data I want to subtotal. For this example, I would like to subtotal a Named Range that I have already defined and called “data”. As before, typing “d” displays the AutoComplete list filtered to items starting with the letter “d”. Notice that the named range, “data” shows up at the top of my list with a different icon. This icon indicates that it is a defined name. All items of the same type will share an icon. Finally, you will notice that the selected item “data” is displaying a tooltip reading “this is the subtotal range”. I added this comment to the name when I created it. All name comments are displayed in auto complete tooltips making it easy to add and view useful metadata for defined names (more soon on name improvements).
After inserting the Named Range “data” and I just need to close the parenthesis.
I hope this simple example shows how helpful AutoComplete can be for building formulas. In addition to requiring less typing, it puts more of the formula building knowledge directly into the product giving customers the luxury browsing their options rather than memorizing them.
One important point to note is that AutoComplete is meant to be a non-disruptive tool for building formulas. Users can write formulas the existing way (by typing them all in by hand) if they choose, and AutoComplete will not stop them. The feature will simply display a list of options matching what they type until there is nothing matching (at which point it will disappear). Also, Formula AutoComplete can be turned on & off from Excel Options or by keyboard shortcut.
What does it autocomplete?
Here is a complete list of the items that will appear in the Formula AutoComplete drop down:
Comments: (30) Collapse
Intellisense in the worksheet. Awesome.
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
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
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
Beautiful ... (wipes tear from eye). I can't wait to start using Office 12!
"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?
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.
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)?
Thanks David - now I understand - I wasn't familiar with the subtotal function from Excel 2003. My mistake, thanks for the clarification.
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.
This just keeps getting better an better - Office 12 is going to be awesome. I just hope I get on the beta.
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?
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?
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.
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
Comments: (loading) Collapse