• Excel Book Excerpt

Excel Yes, Formula Autocomplete Is Cool, if You Can Stop Entering the Opening Parentheses

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Yes, Formula Autocomplete Is Cool,
if You Can Stop Entering
the Opening Parentheses

Problem: At an Excel launch event, the Microsoft rep showed off the amazing new Formula AutoComplete feature. I can just type =RA in a cell, and Excel will show me all the functions that start with RA. I don’t have to type my functions anymore, but why do I get an error every time I try to do this?

LE10000266.jpg

Figure 290 Yes, Formula AutoComplete is cool.

Strategy: Watch the parentheses! AutoComplete types the opening parenthesis, but not the closing parenthesis.

Here is how you’re supposed to use AutoComplete:

1. Type =RA. Excel displays a list of five functions.

2. Use the down arrow to move to RANDBETWEEN. Excel will show a ToolTip to indicate that the function will return a random number between the numbers you specify.

3. Press the Tab key to accept the function and move to the arguments. I was used to using the Tab key here because I’ve been using AutoComplete in VBA for a while. However, many people try to press Enter here, which leads to a #NAME? error. After you press the Tab key, Excel fills in the function name and the opening parenthesis.

LE10000267.jpg

Figure 291 Press Tab to finish typing the selected function name.

Gotcha: I will sound ungrateful, but Microsoft types the opening parenthesis for you. I cannot seem to break the habit of typing the opening parenthesis myself. Going back to the days of typing @SUM(, or even typing =SUM(, my fingers automatically type the opening parenthesis. I cannot type =RANDBETWEEN( without typing an opening parenthesis. Here, let me try a few more: =VLOOKUP( =AVERAGE( =TRIM( =MID( =ROMAN(. My brain is simply hard-wired to type that opening parenthesis. I don’t even consciously think about typing the parenthesis. It simply just gets typed.

So, as you can guess, every time I use AutoComplete, I get an error saying that I’ve typed too many parentheses.

I don’t have a good solution for this, other than trying to retrain yourself not to type the opening parenthesis.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:

privacy policy