How to Auto Update Dropdown List in Excel

We often create a dropdown list based on certain range in excel to filter value recorded in this data range. Usually the original data range is not fixed, we can add more values in the following cells which are not included in the original data range per our demands, and in this case the dropdown list cannot be updated accordingly due to the range defined during creating dropdown list doesn’t cover the new data. This article will help you to solve this problem by two methods, use OFFSET function in data validation, or create a table and define the table name in advance before create dropdown list.

Create a table. It can be seen as the original data.

Auto Update Dropdown List 1

Create a dropdown list based on this range.

Auto Update Dropdown List 2

Add a new name Serena into A9, and let’s see if the dropdown list is auto updated.

Auto Update Dropdown List 3

Obviously, the dropdown list is not auto updated per our expect. Now let’s follow below steps to implement this feature.

Method 1: Auto Update Dropdown List by OFFSET Function in Excel


Step 1: Click Data->Data Validation to load Data Validation window. Verify that previous settings are loaded automatically.

Auto Update Dropdown List 4

Auto Update Dropdown List 5

Step 2: In Source textbox, enter =OFFSET($A$2,0,0,COUNTA(A:A)-1). Then click OK to save the update. In this case, $A$2 is the first cell in original data range and it is also the first cell in dropdown list. After entering the formula, we can ignore that row/column range in dropdown list source range.

Auto Update Dropdown List 6

Step 3: Add a new name Tom in A10 to test if above method works.

Auto Update Dropdown List 7

Verify that Tom is auto added in the dropdown list.

Method 2: Auto Update Dropdown List by Define Table Name in Excel


Step 1: Select the range A2:A10 from original data range, see below screenshot.

Auto Update Dropdown List 8

Step 2: Click Formulas->Define Name.

Auto Update Dropdown List 9

Step 3: In New Name window, enter ‘Range’ in ‘Name’ textbox, keep ‘Refers to’ textbox value (or you can select the range you want to display in dropdown list here), then click OK.

Auto Update Dropdown List 10

Step 4: Select a cell included in the source data, for example we select David. Then click Insert->Table.

Auto Update Dropdown List 11

Step 5: In Create Table window, check on the option ‘My table has headers’. Select =$A$1:$A$10 as the table data. Then click OK.

Auto Update Dropdown List 12

Verify that table is created properly. Name is the header.

Auto Update Dropdown List 13

Step 6: Create dropdown list in C2. Click Data->Data Validation. In Data Validation window, in Allow dropdown list select List, in Source textbox, enter =Range. Then click OK. In this case, Range is the defined name in step 3.

Auto Update Dropdown List 14

Verify that dropdown list is created properly.

Auto Update Dropdown List 15

Step 7: Input new names Henry and Bobbi into table.

Auto Update Dropdown List 16

Verify that dropdown list is auto updated accordingly.

 

You might also like:

Sidebar