Excel Tip - Data Validation Dynamic Lists

A lot of people find the Data Validation List feature useful, as it allows you to have a drop down option to choose the required entry from.  But what if the list of entries is to grow?

Here is an example of where a Data Validation List has been used on column E:


If the selection that populates the list is just a normal RANGE, then any additional entries after the last entry in that list will not be picked up automatically:

Here’s how to do it.

Turn the source list in to a Table first:

  • Highlight the list
  • Select Table from the Insert tab
  • Click OK

Colour bands will be evident in the list, plus an additional yellow tab on the Ribbon will appear:

  • Return to the Data Validation tool for the relevant cells that will have the drop down feature
  • Within the Data Validation dialog box, reselect the Source range
  • Click OK

Now when you add new data to the source list, these will be automatically picked up within the drop down list.

Note: The Data Validation source list must be on the same sheet as the data, unless you use a Range Name.

Pin it