How to Auto Update Drop-Down List in Excel (3 Ways)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for ways to auto-populate the drop-down list in Excel, then this article may be helpful for you. In this article, we will show how we can create an auto-update drop-down list in Excel to make your task easier. As you don’t have to update your drop-down list manually after changing any value in your main data range. So, let’s get into the main article.


How to Auto Update Drop-Down List in Excel: 3 Ways

Here, we have a list of payment types for which we want to make a drop-down list. But for inserting any other payment types further we want to update our drop-down list automatically by following the below stated 3 methods.

auto update drop down list in excel

For creating this article, we have used Microsoft Excel 365 version. However, you can use any other version at your convenience.


Method-1: Using OFFSET and COUNTA Functions to Auto Update Drop Down List in Excel

In this section, we will use the combination of the OFFSET and COUNTA functions for auto-updating the dropdown list consisting of the payment types.

Using OFFSET and COUNTA Functions to Auto Update Drop Down List in Excel

Steps:

Firstly, we will see the effect of creating a drop-down list normally.

  • Select cell D5 and then go to the Data tab >> Data Validation group >> Data Validation.

Then, the Data Validation wizard will open up.

  • Select List from different options under Allow and choose the range in the Source.
=$B$5:$B$10

It is the range of payment options.

  • Press OK.

data validation

Then, you will get a drop-down list and if you add another row for another payment option; Bitcoin, then you can see that the drop-down list is not updated automatically. So, this newly created option is not included in our list.

Using OFFSET and COUNTA Functions to Auto Update Drop Down List in Excel

To solve this problem, we will create our list in the following way.

  • After opening the Data Validation dialog box, select List from different options under Allow and type the following formula in the Source box.
=OFFSET($B$5,0,0, COUNTA(B: B)-1)

Here, $B$5 is the starting cell of the range, the following 2 zeros are indicating that the cell reference will not move by any row or column number. Finally, COUNTA(B: B)-1 is indicating the height number of the range which will be the number of rows having texts or numbers.

  • Press OK.

So, after entering our data validation formula we have got the following drop-down list.

If we add another payment option named Bitcoin, then this option will be automatically added to our list.

Using OFFSET and COUNTA Functions to Auto Update Drop Down List in Excel

Read More: How to Add Item to Drop-Down List in Excel


Method-2: Defining Name for a Range and Creating Table to Auto Update Drop-Down List

There is another way to create an auto-update drop-down list in Excel by defining the name and using a table. We will see this process using the same example above.

Defining Name for a Range and Creating Table to Auto Update Drop-Down List

Steps:

  • Go to the Formulas tab >> Define Name group >> Define Name.

Afterward, you will get the New Name wizard.

  • Input a name into the “Name” Text Box. Here we will input “Payment_Types” and select our payment range in the Refers to.
  • Click on OK.

named range

Now, it’s time to create a table.

  • Go to the Insert tab >> Table.

In this way, you will be taken to the Create Table dialog box.

  • Select the data range, and check the My table has headers option.
  • Press OK.

table

In this way, we created the following table.

  • Now, select cell D5 (where we want our dropdown list), and then go to the Data tab >> Data Validation group >> Data Validation.

Defining Name for a Range and Creating Table to Auto Update Drop-Down List

Then, the Data Validation wizard will open up.

  • Select List from different options under Allow and type the named range in the Source box.
=Payment_Types

It is the range of payment options.

  • Press OK.

data validation

So, after entering our data validation formula we have got the following drop-down list.

list

If we add another payment option named Bitcoin, then this option will be automatically added to our list.

Defining Name for a Range and Creating Table to Auto Update Drop-Down List in Excel

Read more: Create Excel Drop Down List from Table


Method-3: Applying Excel INDIRECT Function with a Table to Auto Update Drop-Down List

In the last method, instead of using the named range in the data validation source.  We can also use the INDIRECT function in the Source box and reference the Table name. In this section, we will show how you can do the same thing as Method 2 using another function.

Applying Excel INDIRECT Function with a Table to Auto Update Drop Down List in Excel

Steps:

First, we converted our data range to a table, and the name of this table is Table3.

table

  • Now, open up the Data Validation dialog box to add a drop-down list in cell D5.
  • Select List from different options under Allow and type the following formula in the Source.
=INDIRECT(“Table3”)

Table3 is the range of payment options.

  • Press OK.

Applying Excel INDIRECT Function with a Table to Auto Update Drop Down List in Excel

So, after entering our data validation formula we have got the following drop-down list.

If we add another payment option named Bitcoin, then this option will be automatically added to our list.

Applying Excel INDIRECT Function with a Table to Auto Update Drop Down List in Excel


How to Auto Populate Drop-Down List in Excel

With the help of a VBA code, we will auto-populate the texts from a dropdown list in cell D5.

Using VBA Code Auto Populate Drop-Down List in Excel


Step-01: Create a Drop-Down List, a Combo Box

First, we will create a simple drop-down list in cell D5.

  • Select cell D5 and then go to the Data tab >> Data Validation group >> Data Validation.

drop-down list

Then, the Data Validation wizard will open up.

  • Select List from different options under Allow and choose the range in the Source.
=$B$5:$B$10

It is the range of payment options.

  • Press OK.

data validation

So, we have created the drop-down list successfully.

list

Now, we will insert a combo box.

  • Go to the Developer tab >> Insert dropdown >> Combo Box (ActiveX Control).

combo box

Then, a plus symbol will appear.

  • Drag down and to the right the plus symbol.

  • We have created a combo box, note down its name for use in the code (the name of the combo box is ComboBox1).
  • Go to the Developer tab >> Design Mode to deactivate the Design Mode.


Step-02: Write VBA Code

Now, it’s time to insert our code.

  • Right-click on your sheet name and click on View Code.

view code

In this way, the Visual Basic Editor window will open up to insert our code.

  • Type the following code in VBE
Sub Worksheet_SelectionChange(ByVal P_val As Range)
Dim DList_box As OLEObject
Dim Ptype As String
Dim Dsht As Worksheet
Dim P_List As Variant
Set Dsht = Application.ActiveSheet
On Error Resume Next
Set DList_box = Dsht.OLEObjects("ComboBox1")
DList_box.ListFillRange = ""
DList_box.LinkedCell = ""
DList_box.Visible = False
If P_val.Validation.Type = 3 Then
P_val.Validation.InCellDropdown = False
Cancel = True
Ptype = P_val.Validation.Formula1
Ptype = Right(Ptype, Len(Ptype) - 1)
If Ptype = "" Then Exit Sub
DList_box.Visible = True
DList_box.Right = P_val.Right
DList_box.Bottom = P_val.Bottom
DList_box.Width = P_val.Width + 90
DList_box.Height = P_val.Height + 10
DList_box.ListFillRange = Ptype
If DList_box.ListFillRange = "" Then
P_List = Split(Ptype, ",")
Me.ComboBox1.List = P_List
End If
DList_box.LinkedCell = P_val.Address
DList_box.Activate
Me.ComboBox1.DropDown
End If
End Sub

Code Breakdown

  • We have chosen the sub procedure name as Worksheet_SelectionChange, because Worksheet is indicating the sheet and SelectionChange is indicating the event which will run the code. And we classified P_val as Range.
  • After that, we declared DList_box as OLEObject, Ptype as String, Dsht as Worksheet, and P_List as Variant.
  • Then we assigned the active sheet to Dsht, and the combo box named ComboBox1 to DList_box.
  • The value of the data validation type is selected as 3 which indicates the drop-down
  • The Ptype variable will store the formula used for data validation in the active sheet.
  • Then, we fixed the position of the drop-down list box and mentioned the size also.

code


Step-03: Get Result While Typing

Now, we will test our code.

  • Now, return to the main worksheet, and click on cell D5 where we have created our drop-down list.

click on cell

  • Start typing C for Cash, then you will notice the Cash name has already appeared in cell D5.

Using VBA Code Auto Populate Drop-Down List in Excel

  • Now, just put your cursor in another place, and so the combo box will disappear again.

Using VBA Code Auto Populate Drop-Down List in Excel

Read More: How to Populate List Based on Cell Value in Excel


How to Edit a Drop-Down List Based on Excel

You can easily edit the range on the basis of which you created a dropdown list.
For example, here we have selected the whole range of the Payment Types column to include all of the options in the list. But, now we want only the last three options on our list. So, we have to edit it.

Edit a Drop-Down List Based on Excel

Steps:

  • Select cell D5 and then go to the Data tab >> Data Validation group >> Data Validation.

Now, the Data Validation dialog box will open up.

  • Here, we can see the following range in the Source box.
=$B$5:$B$10

data validation

  • We have changed it into the following range.
=$B$8:$B$10
  • Press OK.

Finally, we have completed our editing to change the list of options.

Read More: How to Edit Drop Down List in Excel


Practice Section

To practice by yourself, we have created a practice section on the right side of each sheet.

practice section to auto update drop down list in excel


Download Practice Workbook


Conclusion

In this article, we have discussed different ways to auto-update a drop-down list in Excel. Hope these methods will help you a lot. If you have any further queries, then leave a comment below.


Further Readings


<< Go Back to Edit Drop-Down List in ExcelExcel Drop-Down List | Data Validation in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

2 Comments
  1. Reply
    Kanhaiyalal Newaskar. Oct 6, 2021 at 11:07 AM

    Sir, 10th October,2021.

    I liked this article very much, as shown very neatly and clearly.
    I must thank you for taking so much efforts taken by you.
    Hoping to receive more and more articles in Excel in future too.

    Kanhaiyalal Newaskar.

    • Hello, KANHAIYALAL NEWASKAR!
      Thank you so much for your appreciation.
      We also hope to learn and share more and more knowledge with everyone!

      Regards,
      Tanjim Reza

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo