Home » Tips and Tricks » Dynamic Dropdown List

Dynamic Dropdown List

Reading time: 2 minutes
Last Updated on 22/11/2019 by Frédéric LE GUEN

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

1 Comment

  1. zp
    30/05/2021 @ 04:27

    good job bro

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Dynamic Dropdown List

Reading time: 2 minutes
Last Updated on 22/11/2019 by Frédéric LE GUEN

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

1 Comment

  1. zp
    30/05/2021 @ 04:27

    good job bro

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *