How to create an automatically extended or dynamic drop-down list

Excel 365
When you work with data in Excel, you often need to add more and more new data. However, it takes some time to update formulas, charts, tables, when you add new data. Excel proposes functions that can help to customize your data and avoid routine updating. This tip is about creating an automatically extended (also known as dynamically updated or just dynamic) drop-down list.

To create a dynamic drop-down list, do the following:

   1.   Enter the list of items in a range. For example:

List with images in Excel 2016

   2.   Select the cell that will contain the drop-down list.

   3.   On the Data tab, in the Data Tools group, click Data Validation:

Data Validation button in Excel 2016

   4.   In the Data Validation dialog box, on the Settings tab:

  • In the Allow drop-down list, select List.
  • In the Source box, enter the following formula:

    = OFFSET (<first cell>, 0, 0, COUNTA (<column>), 1)

    For this example:

    = OFFSET (Data!$B$3, 0, 0, COUNTA (Data!$B:$B), 1)

    So, you ask Excel to return the data range of filled cells, started from the <first cell> in column (the function COUNTA () returns the number of cells that are not empty in a range).

    Data Validation in Excel 2016
  • Make sure that the In-Cell Dropdown option is checked.

Notes:

  1. You can create your data range in columns. To create a drop-down list from data in columns, use the function COUNTA () as the last parameter.

    For example:

    = OFFSET (Data!$B$2, 0, 0, 1, COUNTA (Data!$2:$2))

    Example of drop-down list in Excel 2016
  2. You can create an incorrect list if you have blank cells in your data range.

   5.   Click OK (see How to show image linked to the list element for more details):

Drop-down list in Excel 2016

After adding new data to the list, the drop-down list has been updated automatically:

Automatically extended or dynamic drop-down list in Excel 2016

See also this tip in French: Comment créer une liste déroulante automatiquement étendue ou dynamique.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.