In Excel, you will find that sometimes the autocomplete of cell values will not work. And in this article, we will dig out the reason.
In Excel, sometimes you need to enter duplicate contents into a column repeatedly. In such a case, AutoComplete feature in Excel will save your time. Whenever you input the first character of an existing string, Excel will complete the remaining for you automatically.
A Case when AutoComplete Works
In this image, you can see that we have already input the phrase “DataNumen Excel Repair” into cell A1. And now we will input the phrase “DataNumen Access Repair” into cell A2. Both phrases start with the same character “D” or “d”. So when we enter the character “D” or “d” into the cell, the existing phrase will automatically appear.
And this is the feature of autocomplete. If you use Excel frequently in your work, you may have already found this feature.
Why AutoComplete Fails to Work?
However, sometimes you may find that even if you input the first character of an existing string, the string will not pop up. And we have find out the reason.
Now we have input the phrases “DataNumen Excel Repair” into A1 and “DataNumen Access Repair” into cell A2. Now in Cell A3, we need to input the same phrase “DataNumen Excel Repair”. But when we type the character “D”, that string will not appear.
Even if we input the “DataNumen” or “DataNumen ”(with the space), it still doesn’t appear.
Till now, many may think the AutoComplete feature stops working, or Excel just expects you to input a different string smartly so it does not perform auto complete any more.
But that is not the case. Actually, there are two different phrases with the same beginning “DataNumen ” (with the space) in Cell A1 and A2. So if you only type part of or the whole “DataNumen ” (with the space) into A3, Excel doesn’t know if you want to input the same value as A1 or A2. Therefore, the autocomplete will not show up. Only when the count of inputted characters is large enough to match ONLY one of the existing strings, the autocomplete will appear.
So in the above case, if we continue inputting the character “E” into the cell, there is ONLY one existing string that matches your input, i.e., “DataNumen Excel Repair”, so AutoComplete will pop up that string again.
On the other hand, we can also solve the uncertainty by telling Excel which string we want directly. Just do as follows:
- Input first several characters of the string.
- Press the shortcut keys “Alt+↓”
- In the drop down list, all existing string with the same characters at the beginning will appear.
- Select the string you want instead of inputting all its characters manually.
Besides, you can also right click the cell and choose “Pick from Drop-Down list” to invoke the drop-down list as well.
Disable the Autocomplete Feature
Sometimes you don’t need the feature of autocomplete, you can also disable it. In the image below, you can see that we input “DataNumen Excel Repair” into A1. And in A2, we want to input another phrase “DataNumen Access Repair”. The autocomplete value will also appear as usual.
And if you press the button “Enter” habitually, you need to come back to the cell and modify again. This will cause inconvenience to you. Thus, you can turn off this feature manually.
- Click “File” in the ribbon.
- And then click “Options”.
- Next you will see the window of “Excel Options”. Here click “Advanced”.
- In the “Edit options” part, uncheck the option “Enable AutoComplete for cell values”.
- Last click “OK”.
Now you have finished all the settings. And if you enter the similar values into the cell, the autocomplete will not appear in cell. In addition, even if the feature is disabled, you can still press “Alt+↓” to open the drop-down list in case you need to input the same contents.
Recovery Tool is a Must for Excel Users
If you use Excel frequently, you will certain meet with Excel corruption at some point. Thus, you are recommended to prepare a third party tool to repair Excel. Once such accident happens, you can use this tool immediately. And your data and information can be well preserved in Excel.
Author Introduction:
Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com