Yes, Formula Autocomplete Is Cool, if You Can Stop Entering the Opening Parentheses
January 18, 2022 - by Bill Jelen
Problem: Formula AutoComplete lets me type just a few characters. 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?
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.
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.
This article is an excerpt from Power Excel With MrExcel
