Mastering Excel: Autocomplete in Drop Down Lists - Tutorial with Pro Tips

In this video you will learn How to auto complete when typing in excel drop down list. or How to create drop down list with auto complete option in excel

Unlock the efficiency of Excel with our latest tutorial on autocompleting in drop-down lists. In this comprehensive guide, we not only walk you through the process of creating drop-down lists but also reveal the powerful autocomplete option, streamlining your data entry tasks.

📊 Video Highlights:

Creating Drop-Down Lists: Learn the fundamentals of creating drop-down lists in Excel for organized and error-free data entry.
Autocomplete Demystified: Discover how to enable and leverage the autocomplete option, saving time and enhancing accuracy.
Customization Techniques: Tailor your drop-down lists and autocomplete settings to match the specific needs of your Excel projects.
Optimizing Data Entry: Explore how autocomplete in drop-down lists boosts efficiency, especially in large datasets.
Pro Tips for Seamless Autocomplete: Gain insights into advanced tips and best practices to make the most of autocomplete in Excel.
Whether you're a data analyst, Excel enthusiast, or a professional seeking to streamline data entry, this tutorial is crafted to enrich your Excel proficiency. Subscribe now, hit the notification bell, and let's master autocompleting in drop-down lists together! 🚀📈

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr

Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("DropListTemp")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
If .ListFillRange = "" Then
xArr = Split(xStr, ",")
Me.DropListTemp.List = xArr
End If
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.DropListTemp.DropDown
End If
End Sub
Private Sub DropListTemp_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 9
Application.ActiveCell.Offset(0, 1).Activate
Case 13
Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub

After that you have to save file with .xlsm extension.

Subscribe Our Channel:

and Facebook page:

[AbdulAleemBaig]

Give me your feedback about this video and the course also give me suggestions and follow-on Facebook and Twitter.
FaceBook:

[hariharanpaptech]

Really it works....Awesome feature to use...since it is not available on desktop version (only insider version-available). But your VB code made available to desktop users. Thanks a lot.

[mirzageeelectronic]

it resolve my problem after 5 years
Thanks a lot of you

[Hopefully]

I can't even tell you how much you helped me, and all with the subtitles!

[sarwaralam]

Zazakallahu Khairan. It helps me a lot.

[jobmascardo]

Abdul, you are heaven sent! Thank you!

[VisualKreation]

Really Great Work and Helpful video sir..TQ

[elizabethjoseph]

Goodness!!!! You literally saved so much of time for me. Hats off to you

[josephrichardlazaro-hsiu]

The data validation restrictions are not working; I need only inputs that are on the list. thank you anyway. its a big help, hope you help me

[BarunKumar-esrp]

Thank you sir, I searched lots of video but nowhere found this coding. It help me lots .

[nirav]

great tutorial bhai.... much appreciated
From India

[amanpreetsingh]

Awesome, I was struggling to get this right since a long, and you solved my problem in 4 minutes. thumbs up .

[galipmogulkoc]

You are great Man! Thank you so much!!

[asaadfayyaz]

جزاک اللہ خیرا
Thank you and keep sharing

[waqasarshad]

Sir I am working to create an excel sheet for a specific purpose mainly covering some business details for me. Your video's topic may also be helpful for me, but I have another query. Can you please help me out? I have hardly found anything close so far.

For some entries in cells, I have used data validation (selected 'List' as validation criteria under 'Allow' tab of Data Validation window) for three options.

In addition to that, I have used Conditional Formatting, made a New Rule by selecting 'Use a formula to determine which cells to format' and used 'Format' button to fill the colors of cells.

The purpose is to identify the cells based on outcome and color of the formatted cell (e.g. Excellent, Good, Poor as three options in drop-down of a single cell).

For the purpose of sharing the information to other, I want to add the comments with each of the drop-down option. Is there a way that can be helpful to auto-populate separate comment based on drop-down selection of data validation options of a single cell?

Thanks in advance.

[tejadageraldt.]

Thank you so much for this very helpful video you shared. I'm really thankful that people like you still exist in this world.♥

[mohammedelsadi]

thanks you very match, its really great code

[akagamingking]

dude how to do this in google sheets??

[md.mahamudulla]

Thanks a Lot Brother. I have Subscribed your channel & I'm also with you from today. Because i learned important things from you Video. Again Thanks a lot.

[livit-fit]

Fabulous work, my friend! Saved me a lot of time and pain, keep up the good work!