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”.

After that, click the button “Data Validation” in the toolbar.

And then click the tab “Data” in the ribbon.

Here we will click a cell A2 in another worksheet.

Click the target cell that you need to create the list.

The image below is the source range in a worksheet. In this method, you can use the OFFSET function in the data validation.

By using those methods, the drop-down list will refresh automatically. But now, we have found two effective methods for you. Whenever you add or delete an item in the range, you need to check the drop-down list in the target cell. When the source range changes, the drop-down list will also be affected. 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.

Excel drop down list