How to Use AutoComplete in Excel (4 Easy Ways)

When we begin entering data into a cell, Excel suggests duplicate values from its related cells, and we can acquire the duplicate values by simply pressing Enter at that point without having to type anything else. It is known as the AutoComplete feature. In this article, I will show you four handy ways to use AutoComplete in Excel.


Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.


4 Simple Ways to Use AutoComplete in Excel

Remember that there is no easy method to alter the AutoComplete in Excel. Excel will operate in accordance with its default settings. However, we have a few other challenging options we can use to perform this operation. For the purpose of demonstration, we have used the following dataset.

Sample Dataset for How to Use AutoComplete in Excel


1. Utilizing AutoComplete for Formula

Indeed, we need to enter formulas into the formula bar. We can use AutoComplete in this scenario. Hence, you need to follow some tricky ways to complete the operation. For example, I have used the following sample dataset.

Sample Dataset for Utilizing AutoComplete for Formula in Excel

Steps:

  • Firstly, start to write the SUBSTITUTE function in the formula bar as the picture shown below. However, it will show the full function below.

=SUBSTITUTE(B5,",","")

  • Secondly, click on the function and press Enter key.

Utilizing AutoComplete for Formula in Excel

  • Thirdly, use the AutoFill tool to get the desired output for the whole dataset.

  • Finally, you will get the total output as shown in the below picture.

Read More: How to Plot an Equation in Excel (6 Easy Ways)


2. Using AutoComplete from Another Row in Excel

Generally, AutoComplete fills in your text automatically while you are typing something. Each time you begin typing in a cell, all the entries in the same column of the active region are examined gradually. Moreover, any potential matches are automatically highlighted as you write each character. Hence, follow the steps below in order to get your desired output.

Steps:

  • At first, start to write the initial alphabet of the text which you want to be completed automatically.
  • Then, Excel will automatically show the complete text string just like the following image.
  • After that, click the Enter button.

Using AutoComplete from Another Row in Excel

  • Lastly, you will find the complete text string as shown in the below image.

Read More: How to Edit AutoComplete in Excel (4 Easy Methods)


3. Applying VBA to Perform AutoComplete

VBA can also help you use AutoComplete in Excel. A VBA code is necessary for this method. Moreover, it will make the process easier and save you time. However, you can just copy the code and run it on your Excel worksheet by following the below steps.

Steps:

  • At first, click on the Insert option from the Developer tab.
  • From the ActiveX Controls option of the Insert command, select a List Box and a Text Box gradually as mentioned in the below picture, and turn off the Design Mode simply by clicking it.

Applying VBA to Use AutoComplete in Excel

  • Secondly, right-click on the Sheet tab and click on the View Code.

  • A new window will open. Write the following VBA Code macro in the code window.
Dim XBk As Range
Dim XWtg As New Dictionary
'ExcelDemy Publications
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Me.TextBox1.Value = Me.ListBox1.Value
End Sub
'Declaring Private Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'Declaring Variables
    Dim XPrm As String
    On Error Resume Next
'Starting If Function
    If IsNumeric(Target.Value) Then
        XPrm = Str(Target.Value)
    Else
        XPrm = Target.Value
    End If
    If XPrm <> "" Then
        If Not XWtg.Exists(XPrm) Then
            XWtg.Add XPrm, XPrm
        End If
    End If
End Sub
'Declaring New Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.ListBox1.Visible = False
End Sub
'Private Sub
Private Sub Worksheet_Activate()
'Declaring New Variables
    Dim P As Long
    Dim xSig As String
    On Error Resume Next
'New If loop
    If XBk Is Nothing Then
        Set XBk = ActiveSheet.UsedRange
    End If
    Me.ListBox1.Visible = False
    XWtg.RemoveAll
    With Me.ListBox1
'Starting For loop
        For P = 1 To XBk.Count
            xSig = XBk(P).Value
            If xSig <> "" Then
                .AddItem xSig
                If Not XWtg.Exists(xSig) Then
                    XWtg.Add xSig, xSig
                End If
            End If
        Next
    End With
End Sub
'New Private Sub
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    With Me.ListBox1
        .Top = Me.TextBox1.Top
        .Left = Me.TextBox1.Left + Me.TextBox1.Width
        .Width = Me.TextBox1.Width
    End With
    TextBoxPrm Me.TextBox1.Object
End Sub
'Declaring New Sub
Sub TextBoxPrm(xTextBox As Variant)
'Declaring Variables
    Dim P As Long
    Dim xSig As String
    On Error Resume Next
    Application.ScreenUpdating = False
'New If Loop
    If XBk Is Nothing Then Exit Sub
    Me.ListBox1.Clear
    xSig = xTextBox.Value
    If xSig = "" Then
        Me.ListBox1.Visible = False
        Application.EnableEvents = True
        Exit Sub
    End If
    For P = 0 To UBound(XWtg.Items)
'Introducing If loop
        If Left(XWtg.Items(P), Len(xSig)) = xSig Then
            Me.ListBox1.AddItem XWtg.Items(P)
        End If
    Next
    Me.ListBox1.Visible = True
    If Me.ListBox1.ListCount > 0 Then
        With xTextBox
            .Value = Me.ListBox1.List(0)
            .SelStart = Len(xSig)
            .SelLength = Len(Me.ListBox1.List(0))
        End With
    End If
    Me.ListBox1.Activate
    Me.ListBox1.Selected(0) = True
    Application.ScreenUpdating = True
End Sub
'New Private Sub
Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 13 Then
        Me.TextBox1.Value = Me.ListBox1.Value
    End If
End Sub

VBA Code to Use AutoComplete in Excel

VBA Code Breakdown

  • Firstly, we declared variables XBk, XWtg, Xprm and create some new Private Sub in the worksheet using the below statement.
Dim XBk As Range
Dim XWtg As New Dictionary
'ExcelDemy Publications
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Me.TextBox1.Value = Me.ListBox1.Value
End Sub
'Declaring Private Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'Declaring Variables
    Dim XPrm As String
  • Secondly, we stated If Function and ended the Sub.
On Error Resume Next
'Starting If Function
    If IsNumeric(Target.Value) Then
        XPrm = Str(Target.Value)
    Else
        XPrm = Target.Value
    End If
    If XPrm <> "" Then
        If Not XWtg.Exists(XPrm) Then
            XWtg.Add XPrm, XPrm
        End If
    End If
End Sub
  • Thirdly, we declared variables p, XSig and set
'Declaring New Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.ListBox1.Visible = False
End Sub
'Private Sub
Private Sub Worksheet_Activate()
'Declaring New Variables
    Dim P As Long
    Dim xSig As String
    On Error Resume Next
'New If loop
    If XBk Is Nothing Then
        Set XBk = ActiveSheet.UsedRange
    End If
    Me.ListBox1.Visible = False
    XWtg.RemoveAll
    With Me.ListBox1
  • Now, we started a For loop and declared some new Sub.
'Starting For loop
        For P = 1 To XBk.Count
            xSig = XBk(P).Value
            If xSig <> "" Then
                .AddItem xSig
                If Not XWtg.Exists(xSig) Then
                    XWtg.Add xSig, xSig
                End If
            End If
        Next
    End With
End Sub
'New Private Sub
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    With Me.ListBox1
        .Top = Me.TextBox1.Top
        .Left = Me.TextBox1.Left + Me.TextBox1.Width
        .Width = Me.TextBox1.Width
    End With
    TextBoxPrm Me.TextBox1.Object
End Sub
'Declaring New Sub
Sub TextBoxPrm(xTextBox As Variant)
'Declaring Variables
    Dim P As Long
    Dim xSig As String
    On Error Resume Next
    Application.ScreenUpdating = False
  • After that, we compiled some If loops.
'New If Loop
    If XBk Is Nothing Then Exit Sub
    Me.ListBox1.Clear
    xSig = xTextBox.Value
    If xSig = "" Then
        Me.ListBox1.Visible = False
        Application.EnableEvents = True
        Exit Sub
    End If
    For P = 0 To UBound(XWtg.Items)
'Introducing If loop
        If Left(XWtg.Items(P), Len(xSig)) = xSig Then
            Me.ListBox1.AddItem XWtg.Items(P)
        End If
    Next
    Me.ListBox1.Visible = True
    If Me.ListBox1.ListCount > 0 Then
        With xTextBox
            .Value = Me.ListBox1.List(0)
            .SelStart = Len(xSig)
            .SelLength = Len(Me.ListBox1.List(0))
        End With
    End If
    Me.ListBox1.Activate
    Me.ListBox1.Selected(0) = True
    Application.ScreenUpdating = True
End Sub
  • Finally, we end the Sub of the VBA as
'New Private Sub
Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 13 Then
        Me.TextBox1.Value = Me.ListBox1.Value
    End If
End Sub
  • From the code window, click on Tools and then References.

  • Select the Microsoft Scripting Runtime box in the References – VBAProject and click OK.

  • After that, press Alt+Q to close the VBA window.
  • Finally, write the initials of a text in the worksheet and it will automatically show the matched text string as shown in the image below.


4. Using AutoComplete from Another Sheet

Sometimes, we need to get data from another sheet. In this case, AutoComplete is a general solution. For example, we have taken AutoComplete from the other column sheet as a reference. Hence, follow the steps below in order to complete the operation.

Steps:

  • Firstly, select a cell and write down the following formula.

='[Sheet2]AutoComplete from Other Column'!B5

  • Now, press Enter.

Using AutoComplete from Another Sheet in Excel

  • Then, a dialog box will open. Hence, you have to select the file where you want to apply AutoComplete and press OK.

  • After that, you will get the value from the other sheet. However, utilize the AutoFill tool for the entire column.

  • Finally, you will get all the data from another sheet similar to the picture below.

Read More: How to Turn on Autocomplete in Excel (3 Easy Ways)


What to Do If AutoComplete Feature Does Not Work in Excel?

Occasionally, you will discover that the autocomplete feature for cell values in Excel fails. In this part, I will show you a scenario where the AutoComplete fails to provide your desired output. Take a look at the picture below in order to have a clear idea.

Reason for AutoComplete Not Working in Excel

Here, I need to search for the fruit Grape next to Guava in the string. But if I write “Gr” in the last row, it will not show the whole word “Grape.” The AutoComplete will not work in this case.


Conclusion

These are all the steps you can follow in Excel to use AutoComplete in Excel. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

For more information like this, visit Exceldemy.com.


Related Articles

Mehedi Hasan

Mehedi Hasan

Hi, I am Mehedi. I have completed my B.Sc. from Bangladesh University of Engineering and Technology. I have a strong interest in innovation and research in the field of Data Science and Machine Learning. Gradually, I now understand the value of Data Analysis and I am trying to learn everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo