By using the function, the worksheet will not be in a mess.ġ. But still they have advantage sand disadvantages. Otherwise there will be a blank item in the list.īoth of the two methods are very effective. When you need to delete an item, remember to delete the table row. The next time when you add a new item in the source range, the drop-down list will also update. Next click “OK” to save the data validation.Īnd now you have finished the setting.This is the define name that you have created in step 4. Repeat the step 2-4 in the previous part.Now click the target cell that you need to create the drop-down list.Next click the button “OK” to save the setting.In the “Create Table” window, check the option of headers according to your need.After that, click the button “Table” in the toolbar.And then click the tab “Insert” in the ribbon.In this step, click a cell within the source range.Next click the button “OK” to save the range.And then input the range into the “Refers to” text box.After that, click the button “Define Name” in the toolbar.And then click the tab “Formula” in the ribbon.Method 2: Define Name and Use TableĮxcept for using formula, you can also create a table in the worksheet and define name for this range. But by using this method, you need to make sure that there is no additional item in the same column or blank item within the range. The next time you need to create a drop-down list and refer to another range, you can use this method. In the drop-down list, you can also see 7 items. For example, we add a new item into the original range in cell A8. The next time when you add or delete an item in the source range, the items in the list will automatically update. Thus, the drop-down list has been created in the cell. And then click the button “OK” in the ribbon to save the setting.You can change certain elements in the formula according to the actual worksheet. And then input this formula into the “Source” text box:.In the new pop-up window, choose the “List” in the text box of “Allow”.
In our previous article How to Create Drop-down List from a Range of Cells in Your Excel, we have introduced the drop-down list in detail. In this article, we will introduce two methods to auto refresh the drop-down list. The drop-down list in data validation is a frequently used feature in Excel.