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.


Related Posts

Match Single Criteria in Excel

No repeat statistic based on a single condition is used wildly in daily work, for example, count type of products, the duplicate types are not encountered. This article describes the way of no repeats statistics for data in a single ...

Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays. Generate Array with ROW or COLUMN Functions Array ...

Excel Array Operation

We have all heard of arrays. Depending on the dimensionality, they are divided into one-dimensional arrays and two-dimensional arrays. Depending on the data type, they can also be divided into numeric arrays, logical arrays, and so on. The concatenation and ...

Excel Array Matrix Operation

In Excel, we have a built-in function used to perform matrix operations. It is the MMULT function. This function accepts two arrays as parameters and returns the product of the two arrays. However, this function has a prerequisite that the ...

A1 Reference Style and R1C2 Reference in Excel

In Excel, there are two reference styles. A1 reference style and R1C2 reference style. These two reference styles can help us find the cell by the cell address. Some functions return these two addresses through the argument settings. A1 Reference ...