How to Add a Drop Down list using a Named Range in Excel

Previously we looked at making a simple dropdown list but this can be somewhat limited and time consuming if you need to add more options to choose from for any category. Here we will show you the benefits of using Name Ranges or Dynamic drop downs.

Here we have our simple table of Tshirt Orders which shows the Countries they have been ordered from, Brand, Size, Colour and the Quantity Ordered.

Lets say we get an order from a new country that was not in the initial dropdown and you want to incorporate it into the dropdown list. Using the simple dropdown method you would have to go the range on the list tab and change the data validation parameters.

However if we use this new method it means you never have to go through that process again and your table will automatically pick up any new options for the dropdowns.

Note there are two ways of doing this and we are going for the much simpler way that doesn’t involve using an offset formula, if you would like to do this using the offset formula put a comment below and I will send you a how to.

1. Highlight the range that your initial dropdown list was in
2. Click CTRL+T (This creates a table, you can also create a table by clicking Insert on the ribbon and then selecting table)
3. Make sure you have the “My Table has headers” checked and click ok. 
4. Select the whole table again and in the top left it will say “table” and a number

5. Change this to something meaningful, we will call it “Countries”
6. Go back to your main page and highlight the cells where you want to insert the dynamic drop down list
7. Go to Data Validation (Data on the ribbon then select Data Validation)
8. Under Source you will now enter = and the named range, in this case =countries
9. Click Ok

There short videos below which will help you along the way and there is also the link to the full youtube tutorial

 Here is the full youtube tutorial should you need it