By creating a dynamic drop down list in excel, you can save your ton of time & hard work.
As we know drop-down lists are super useful, and if you create a dynamic drop down list, it will work like a magic.
With dynamic drop down list, when you enter new data in your list, your drop down will update automatically.
Here in this post, I’ll show you two quick ways to create a dynamic drop down list.
But first of all, let me tell you why you need a dynamic drop down list.
In above example, I have two different drop-down lists in which I have used the same data source.
When I added a new entry into the source list, that entry is automatically updated in my drop down list.
But, there is no change in the normal drop down list. And, if you want to update your list, you have to update your data source.
That’s why dynamic drop down list is a time saver.
Ahead of this post, I’ll show you two super quick method to create a drop down list with dynamic range.
Methods To Create a Dynamic Drop Down List
If you are using Excel 2007 or above you can use excel tables and in case you are still with Excel 2003, you can use offset function for it.
Using a Table
Using a table to create a dynamic drop down is super easy.
I’m using the list below to create a drop down list.
Here are the steps.
- First of all, change your normal range into an excel table.
- Select you list.
- Go to -> Insert Table -> Tables -> Table.
- Click Ok.
- Now, next step is to create a named range to use in the drop-down list.
- Go to Formulas -> Name Manager -> Click New.
- Enter “monthList2007” in name input box.
- The scope should be in “Workbook”.
- In “Refer To” select the entire column of your table.
- Click OK.
- Finally, we can create our drop down list with this dynamic range.
- Select the cell where you want to insert your drop down.
- Go to -> Data Tab -> Data Tools -> Data Validation.
- In setting tab, select the list option from “Allow”.
- And, in source input enter named range.
- Click Ok
Now, you have a dynamic drop down list which will update instantly when you update your source list.
Using OFFSET Function for Dynamic Drop Down List
Well, if you are still using Excel 2003 or a lower version, you can use offset function to create a dynamic range.
And, then you can use that dynamic range for creating a dynamic drop down list.
Here are the steps.
- Go to Formulas -> Defined Names -> Name Manager -> Click New.
- In name input box enter a name for named range (Here I am using “monthList2003”).
- Enter below formula in “Refers To”.
- Click Ok.
- Now, create a drop down list by using the dynamic range you have created.
How does this work?
The formulas which I have used to create a dynamic range will count the values from the list and expand the source range accordingly. This will make your drop down list dynamic.
Download this sample file to learn more about using a dynamic drop down lists.
Using a dynamic drop down list is a time saver.
If you need to add new entries in you source list continually, you must have to use a dynamic drop down list.
With a dynamic drop down list “Life is Good” because it’s a one-time setup.
Over To You
I hope you found this tip useful.
And if you have any other way to create a dynamic drop down list, please share with me.
I would love to hear from you.
See you in the comment section.
What Is Next?
Have you ever heard about Excel 3D Reference? It’s a quick time saver like a dynamic drop down list.