Overview
Drop-down lists are a great way to ensure quality data and make it easy for your users to enter data consistently but restricting the values they can choose from. While you can easily add a static list, it’s much more efficient and scalable to link your drop-down values to a dynamic list that automatically grows as the list changes.
In this article, we’ll explore how to build a dynamic drop-down list and highlight two macros that help you do it very quickly and easily.
Building Dynamic Drop-Down Lists
Making dynamic drop-down lists is a 3-step process.
Step 1: Build the list
I like to keep all my drop-down lists on one reference sheet, but you can put them wherever you’d like in your workbook. Simply make a list where each item is on a different row, and give it a heading at the top. Don’t put anything below the last item on the list – we want to be able to add to the list. See an example with the list of colors in the screenshot below.
Step 2: Give the list a dynamic named range
To name your list, go to the Name Manager in the Formulas ribbon tab. Click the New button and give it a name. Leave the scope as “Workbook” and in the “Refers to:” box, enter the formula below, updating the references where your list is located (displayed in red). In the $B$3:$B$514 reference, just ensure you leave enough space so that your list will never go past the bottom cell. I tend to use about 500 cells below the top, just in case.
Note: an alternate approach to using a formula-driven named range is to use a table, which can also automatically grow as you add new data.
=OFFSET(Sheet1!$B$3,0,0,MAX(COUNTA(Sheet1!$B$3:$B$514),1),1)
See an example of a dynamic named range formula entered in the screenshot below.
Step 3: Associate the dynamic named range in your data validation
Select one or more cells for which you’d like to add a drop-down list linked to your new dynamic named range. On the Data ribbon tab, select Data Validation.
For the “Allow:” field, select the “List” option. In the “Source:” field, enter the name of your new list with an equals sign before it. See the screenshot below for an example.
Using the dynamic drop-down list
Once you’ve added your dynamic drop-down list, you should see an arrow pop up on the right side of the cell(s) when they are selected. To display the list, just click the arrow or use the keyboard shortcut Alt+Down Arrow.
Your list should display, and if you make changes to the source list, you should see them reflected in the drop-down list! See an example of a drop-down list displayed in the screenshot below.
Macros That Help
I build dynamic drop-down lists all the time – so often that I ended up writing a couple of macros that make them very easy to add in just a couple of clicks!
Named Range Picker – among other options, this will automatically add the named range with the dynamic formula components similar to above. If you have a name for your range in the clipboard, it will automatically be included as the default name to apply! Once you confirm the name to add, it will keep that name in the clipboard so it can be used again.
Add Data Validation From Named Range – this will automatically add the named range as the source for a data validation drop-down list for any cells you have selected.
Summary
Data validation is a crucial tool to use in your Excel files, and drop-down lists are a great way to restrict values and make them easy to use for your audience. As demonstrated in this article, make them dynamic to ensure you don’t forget to update your drop-down list to reflect new values. To apply these settings super-efficiently, keep in mind the two macros that set them up with a lot fewer clicks. The next time you need to set up drop-down lists, remember these approaches!
What tricks do you have for using data validation in Excel? Let us know in the comments below!
Recent Comments