Bottom Line: Learn to create dynamic data validation lists. These in-cell drop-down lists automatically expand to include new rows that are added to the source data range.
Skill Level: Intermediate
Download the Excel File
You can download the file I’m using in the video to practice on your own.
Dynamic Lists with Excel Tables and Named Ranges
Data Validation lists are drop-down lists in a cell that make it easy for users to input data. If you’ve never worked with data validation lists before, I suggest you start with this tutorial for creating drop-down lists in cells before moving on.
In today's post, I want to show you how to make your drop-down list dynamic. In other words, your list can automatically be updated with new options when you add or subtract entries to your source range.
This is done in three simple steps:
- Formatting the source range to be an Excel Table.
- Naming the range.
- Telling the Data Validation rules to pull the named range as your source.
I’ll explain in more detail below.
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 (“Products”), 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.
If you haven’t used Tables before, I recommend checking out my Excel Tables Tutorial Video.
Step 2 – Create the Named Range
The next step in our process is to name our range for the “Products” 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 “rng” 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.
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.
I presented this technique in my article on dependent drop-down lists.
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. Since multiple cells could be using this reference, it makes maintenance easier if you forget to press the “Apply these changes to other cells…” checkbox. Therefore, I recommend using the named range method described above.
Dynamic Data Validation Lists Save Time
By making your Data Validation lists dynamic, you don’t have to worry about updating your drop-down lists every time you make a change to your source data. This not only saves you time but hopefully adds a little peace of mind to your day. 😊
If you are interested in creating drop-down lists that are dependent on other drop-down lists, you can find out how to do that here. For example, if you choose Fruit in your first list, the options to choose from in your second list might be Banana, Apple, and Orange. But if you change your choice to Vegetable in your first list, the options in the second list change to Celery, Carrot, and Broccoli. This function is helpful if your lists drill down several levels or involve steps.
I've also created a free tool that helps you to search a data validation list, so you don't have to scroll through lots of entries to find the one you want. You can get that free search tool here.
I hope all of this information is helpful! If you have any questions about making your drop-down lists dynamic, please let me know in the comments below.