Formula editing improvements Part 2: Formula AutoComplete
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:
- Help users build formulas faster
- Help users build formulas without needing to rely on external help
- Accomplish goals 1 and 2 in a way that is not intrusive or annoying
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 works
Formula 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.
(Click to enlarge)
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.
(Click to enlarge)
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).
(Click to enlarge)
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:
- Excel functions – this includes all functions in the Excel library
- User defined functions – Functions defined by the user either through VBA, automation add-ins, or .XLL’s show up as functions
- Defined Names – All defined names are surfaced
- Enumerated Arguments – Some enumerated arguments (like the function_num argument in the example) will have auto complete support
- Table Structure References – More on this in post coming soon about improved table support.
