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

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

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?

  1. 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.

  2. 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.