This article will show you how to automatically update your drop-down list

Create a dropdown list in Excel

To create a dropdown list activate the menu Data > Data Validation

1. Select a cell (in our example F1)
2. Select the menu Data > Data Validation
3. In the dialog box, select the option List
4. Then select the cells that contain your data
5. Validate
   

Now your cell (F1) displays a small arrow. Click on it to display the list of the data selected.

But now, if you add new values in your source, they will not be display in your dropdown list.

The reason is simple. The range of cells of your dropdown list is still D2:D5 (and it should be D2:D6).

So, in order to create a dynamic dropdown list, we must use a Table, because with the tool Table, the range of data are dynamic.

Create a dynamic dropdown list

To make a dropdown list dynamic, use the Table tool.

1. Select your data source
2. Activate the tool Insert > Table
3. Very important: Check the option My table has headers

1. Re-open the tool Data > Data validation
2. Change the source with the following formula:

=INDIRECT("Table1[Name]")

And now your data source is dynamic 😍😍😍

Tutorial video