Excel - Data Validation in Depth - Create Auto Updating Drop-Down Lists

In this video I explain how to include new items in Excel drop-down lists automatically. These are referred to as Dynamic Data Validation Lists because they automatically extend to include new rows/cells added to the source data range.

In this example we will use Excel Tables and named ranges to create the dynamic lists. The named range references a single Table column using structured references (Table Name[Column Name]). The named range includes all cells in the column and never needs to be updated or maintained.

This means we don't have to remember to update the source data range every time we make changes to it. It saves us a lot of time and helps prevent errors.

Step 1 – Format the Source Range as a Table

To begin, we will format our source range to be an Excel Table. On the Insert tab, you’ll chose the Table button. The keyboard shortcut for inserting a Table is Ctrl + T

The Create Table window will appear, showing the range of cells that will be in your Table. Since our column begins with a header (“Department”), we want to make sure the checkbox that says “My table has headers” is checked. If we don't check that box, the column title will be included in our source range and will appear as one of the options in our drop-down list.

Step 2 – Create the Named Range

The next step in our process is to name our range for the “Department” Table that we just created.

On the Formulas tab in the ribbon, you want to select the Name Manager (or you could use the the Ctrl+F3 keyboard shortcut instead).

The Name Manager window will appear, and you will want to click on the New button.

This brings up a new window that allows you to name your range. I like to prefix my ranges with “ListDep” to make them easier to find in formulas. However, the naming is completely up to you.

The “Refers to” field allows you to select the range that you want to include. The up arrow icon to the right of that field takes you to the worksheet. There you can highlight the selection that you want to use for your range.

Once you have defined your range, you can click OK, and then close the Name Manager window.

Step 3 – Reference the Named Range in the Data Validation Source

Now that we’ve named our range, we just need to tell that name to the Data Validation window so that it knows where to pull from for our drop-down list.

To do that, start with the cell that you want the drop-down list to be added to. Then access the Data Validation window by selecting the Data tab on the ribbon and clicking on the Data Validation button.

In the Data Validation window, under the Settings tab, we can type the name of our range into the Source field.

A shortcut to typing the name of our range is F3, which brings up a list of any ranges we’ve named. We can simply click on the range we want in order to select it.

Once you have selected the named range you want, click OK. But before leaving the Data Validation window, you want to check the checkbox that says “Apply these changes to other cells with the same settings.” This ensures that your drop-down list will be applied to similar cells in your worksheet.

After we click OK, our drop-down list is now pulling from the Table that we have defined and named. So now any time you alter the Table, adding or deleting rows, the drop-down list will remain in sync with those changes.

Alternative to Named Ranges – INDIRECT

There are alternatives to using the named range in the data validation source. We can also use the INDIRECT function in the Source box, and reference the Table and Column name. The formula uses structured reference Table formulas, and looks like the following.

=INDIRECT("TableDep[Department]")

You can type the formula directly in the source box in the Data Validation window. Just make sure the Table name and column name are correct.

One drawback with the INDIRECT technique is that if you change the Table or Column names, the formula will NOT be updated. The drop-down button will still appear next to the cell, but you won't be able to click it.

Using the named range technique does allow you to change the name of the Table or Column. The reference in the named range will automatically be updated. Therefore, I recommend using the named range method described above.

Download the workbook here:
https://drive.google.com/open?id=1WWe8vwpB7ejHeWuekerhbQMplUaaY79P

Tags:
Data Validation Auto Updating DropDown List In Excel, dropdown lists that refresh automatically, create auto updating dropdown list in excel, data validation dynamic list, automatically extend drop-down list, drop-down list in excel change values, add new items to data validation list, Pick Lists, data validation, data validation list, data validation in excel, drop down list, data validation list in excel, data validation drop down list, dynamic data validation drop-down list