Auto Update Drop Down List in Excel (3 Ways)

Auto Update Drop Down List in Excel

In MS Excel, we usually create a drop-down list for some daily work. By default, we can add new data in a cell among the range of original data, then the relative drop-down list will be auto-updated. But if we add new data in the cell below the original data range, the relative drop-down list cannot be updated. In this article, I will show how we can create an auto-update drop-down list in Excel.

Download the Practice Workbook

3 Ways to Auto Update Drop Down List in Excel

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

Let’s say we have a list of payment types. Using this list there is a drop-down list where we can choose the payment options.

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

Now if we to new data in the given list, the drop-down list will not be updated automatically.

Now our task is to create an auto-update drop-down list so that if any data in the list is updated, then the drop-down list will also be updated.

Step 1: Open Data Validation from Data tab

Auto Update Drop Down List in Excel

Step 2: In the Setting tab select do the following things:

  1. Select List for Allow option
  2. In the Source, box write the following formula
    =OFFSET($B$5,0,0,COUNTA(B:B)-1) 
  3. Then press the OK button

Auto Update Drop Down List in Excel

Step 3: Now add new items to the list and find them automatically in the drop-down list

Auto Update Drop Down List in Excel

 

2. Dynamic Drop-Down List in Excel by Defining Name and Use Table

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

Auto Update Drop Down List in Excel

Step 1: Select the source table first then Click on the Define Name option from the Formulas tab

Auto Update Drop Down List in Excel

Step 2: Next you will see a new window. Input a name into the “Name” Text Box. Here we will input “Payment_Types” and make sure the range is correct. Click on the Ok button

Auto Update Drop Down List in Excel

Step 3: Click a cell within the source range. And then click the tab “Insert” in the ribbon. After that, click the button “Table” in the toolbar

Auto Update Drop Down List in Excel

Step 4: In the “Create Table” window, check the option of headers according to your need.

Next, click the button “OK” to save the setting

Auto Update Drop Down List in Excel

Step 5: Now create a drop-down list using the selected data and give the Source as

=Payment_Types

Auto Update Drop Down List in Excel

Step 6: Now add more items to the list and they will be automatically added to the drop-down list

Auto Update Drop Down List in Excel

3. Auto Update Drop Down List in Excel Using INDIRECT Function

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 and Column name. In this section we will how we can do the same thing as method 2 using another function.

Auto Update Drop Down List in Excel

Step 1: Now open the Data Validation window again and write the below formula into the Source box

=INDIRECT("Payment_Types")

Auto Update Drop Down List in Excel

Step 2: Now add more items to the list and they will be automatically added to the drop-down list

Auto Update Drop Down List in Excel

Importance of Dynamic Data Validation Lists

By making your Data Validation lists dynamic, you don’t have to worry about updating your drop-down lists every time you make a change to your source data.  This not only saves you time but hopefully adds a little peace of mind to your day.

Notes

How to Edit a Drop-Down List Based on a Named Change

To edit a drop-down list based on a named change we need to follow the steps:

  1. Select the worksheet that has the named range for your drop-down list.
  2. Do any of the following as per your need:
    1. To add an item, go to the end of the list and type the new item.
    2. To remove an item, press Delete.
  3. Go to Formulas > Name Manager
  4. In the Name Manager box, click the named range you want to update.
    Auto Update Drop Down List in Excel
  5. Click in the Refers to box, and then on your worksheet select all of the cells that contain the entries for your drop-down list.
  6. Click Close, and then click Yes to save your changes.

How to Edit a Drop-Down List Based on a Range of Cells

  1. Select the worksheet that has the data for your drop-down list.
  2. Do any of the following:
    1. To add an item, go to the end of the list and type the new item.
    2. To remove an item, click Delete.
  1. On the worksheet where you applied the drop-down list, select a cell that has the drop-down list.
  2. Go to Data > Data Validation.
  3. On the Settings tab, click in the Source box, and then on the worksheet that has the entries for your drop-down list, select all of the cells containing those entries. You’ll see the listed range in the Source box change as you select.
  4. To update all cells that have the same drop-down list applied, check the Apply these changes to all other cells with the same settings box.

Conclusion

These are some ways to create an auto-update drop-down list in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

1 Comment
  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.

Leave a reply

ExcelDemy
Logo