Dynamic Dropdown List

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
How to create a dropdown list with a range of cells

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

Dropdown list in Excel

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

How to create a dynamic dropdown list

Tutorial video

Related posts


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

Leave a Reply

Your email address will not be published.