Hello Excellers, and welcome to another #formulafriday blog post. I want to share with you an issue one of my subscribers had. Their Excel was not behaving as ‘normal’. When they began to enter a formula, the formula autocomplete in Excel did not appear as expected. Normally when the equals sign is entered into Excel, a formula is expected. The user can then begin to type a formula and Excel displays a dynamic drop list of valid functions, arguments, defined names, tables names etc.
Entering a Formula Using AutoComplete.
Look at my example below. When I type =VLOOKUP, Excel prepares the arguments I need to enter to complete a valid VLOOKUP formula. For example, Excel expects a lookup_value, a table_array, a column_index_num and finally, an options range lookup value.
Turning Autocomplete On or Off – Checking Status of Formula Autocomplete In Excel.
So, if your formula is nIf your formula is not bringing up the autocomplete Excel dialog box when you type the equals sign, you need to check the status of this particular option. Or, if you are a formula pro, then you may want to turn it off altogether if the dialog box is annoying you or getting in the way. (Some Excel users do turn off this function).
In Excel 2016 onwards follow the instruction below to turn Formula Autocomplete in Excel on or off
- Select File
- Goto Options
- Navigate to the Formulas option
- In the Working with formulas area untick or tick Formula AutoComplete
Are You A Keyboard Shortcut Guru?
If you are more of a keyboard shortcut guru then you can easily navigate around autocomplete drop down box or dialogue box. Use the shortcuts below to easily navigate and select the numerous options you want to use.
| KEY | USED FOR |
| Alt+Down Arrow | Turn autocomplete on or off |
| Left Arrow | Moving the cursor point to the left |
| Right Arrow | Moving the cursor point to the right |
| Up Arrow | Moving the cursor point up |
| Down Arrow | Moving the cursor point down |
| End | Selecting the last item |
| Home | Selecting the first item |
| Page Down | Moves a page down and |
| Page Up | Moves a page up and selects item |
| Esc | Closes the drop-down list |
So, if you having issues with the autocomplete Excel dialogue box then you can check the formula autocomplete options. Choose to turn this on or off as you need it. Let me know in the comments below if you use or do not use autocomplete.
To read all of my Formula Friday and Macro Monday Excel Blog Posts bookmalr my links below.
What’s Next? Do you Want Even More Excel Tips?.
I just want to say that I am really pleased to have teamed up with Excel Rescue. If you need help with Excel Fast, Just say the word and they will help. Why not check out their link below.
