Dynamic Dropdown List

This article will show you how to automatically update your dropdown 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.

Note: If you add a new values in your source, it will not added in the data validation.

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 😍😍😍

Explanation of the formula

Firstly, it is important to know the name of your Table. By default, Excel names Tables with the following names: Table1, Table2, .... But if there are already Tables in your worksheet they may have different names.

To find the Table name :

  1. Select one of the cells in the Table
  2. Go to the tab Design (last tab to the right)
  3. The name of the Table is displayed on the top of the ribbon

Write the reference of the column Name inside the Table like this:

Table1[Name]

But this writing doesn't work 😡😤💥. However, if you insert this reference in the INDIRECT function, it works 😍😃👍

Tutorial video

Permanent link to this article: https://www.excel-exercise.com/dynamic-drop-down-list/


Leave a Reply

Your email address will not be published.