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.
Create a dropdown list based on this range.
Add a new name Serena into A9, and let’s see if the dropdown list is auto updated.
Obviously, the dropdown list is not auto updated per our expect. Now let’s follow below steps to implement this feature.
Table of Contents
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.
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.
Step 3: Add a new name Tom in A10 to test if above method works.
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.
Step 2: Click Formulas->Define Name.
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.
Step 4: Select a cell included in the source data, for example we select David. Then click Insert->Table.
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.
Verify that table is created properly. Name is the header.
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.
Verify that dropdown list is created properly.
Step 7: Input new names Henry and Bobbi into table.
Verify that dropdown list is auto updated accordingly.
