How to Create a Dynamic Drop Down List in Excel

A drop down list is a powerful tool.

It can help you to make your data entry simple and quick.

You can use a drop down list with your dashboards, charts or even with normal data.

While using it, one thing always gave me nuts that you need to update its data source whenever you add a new entry.

The more you add data he more frequently you need to update it.

For this, the best solution is to use a dynamic drop down list in which you don’t need to update data source again and again.

But first of all, let me show you an example that why your drop down list should a be a dynamic one.

In below example, we have two different drop down lists in which we have used the same data source.

different between dynamic drop down list in excel and normal drop down list

When you added a new entry into the source list, that entry is automatically updated in the second drop down list.

On the other hand, there is no change in the normal drop down list. And, if you want to update your list, you need to update your data source range.

If you are using Excel 2007 or above then you can use an excel table and in case you are still with Excel 2003, you can use a named range.

1. Using Excel Table to Create a Dynamic Drop Down List

The best method to create a dynamic drop down list is using an Excel table for source data.

list using to create a dynamic drop down list in excel.

Here are the simple steps to create a dynamic drop down list in Excel.

  • First of all, change your normal range into a table.
    • Select your list.
    • Go to ➜ Insert Table ➜ Tables ➜ Table.
    • Click OK.
apply table to create dynamic drop down list in excel
  • Now the next step is to refer to the table range data source and for this we need to use below formula. 

=INDIRECT(“Table5[Month]”)

use indiect function to refer to the table range to create a dynamic drop down list
  • In the end, click OK.

Now you have a dynamic list which will update instantly when you update your source list.

Note: If you try to directly refer the table range as a source it will return an error like below.

error while referring to the table range to create a dynamic drop down list

2. Dynamic Range for a Drop Down List with OFFSET Function

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 drop down list.

Here are the easy steps to create a drop-down list using a dynamic range.

  • 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” and click OK.
create a dynamic range to create drop down list for excel 2003
  • Now you have a dynamic range "monthList2003" and you can use this range name to create a dynamic drop-down list.
use dynamic range to create dynamic drop down list for 2003

How does this work

The formula which we 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.

Sample File

Download this sample file from here to learn more.

Conclusion

Using a dynamic drop down list is a big time saver. And, the best part is, it’s a one-time setup, you don’t need to apply it again and again.

If you need to add new entries in you source list continually, you must have to use a dynamic drop down list.

I hope this tip will help you to get better in Excel but now you need to tell me one thing.

Have you ever used a dynamic drop down in Excel? 

Please share your views with me in the comment section, I'd love hear from you and make sure to share this tip with your friends.

Share0
Tweet0

Related Tips

  1. Dependent Drop Down List in Excel: A dependent drop down is all about showing values in a drop down list according to the selection [...]
  2. Star Rating Template in Excel: By using REPT (formula) you can simply create a start rating template in Excel. It can be useful in […]
  3. Hide Formula in Excel: Hiding a formula is a simple way to do this so that others can’t able to see which formula [...]
  4. Highlight Blank Cells in Excel: It’s recommended to highlight these cells with a color so that we can recognize them [...]
  5. Connect a Single Slicer with Multiple Pivot Tables: The best way to filter more than one pivot tables jointly is to use a single slicer and here are the simple steps which you can use […]
  6. Goal Seek in Excel: In simple words, if you know what actual result value you want, you can use goal seek to find [...]
  7. Data Entry Form in Excel: In Excel, there is a pre-built user-form which you can use to enter your data into a worksheet and the best part is there […]
  8. Watch Window in Excel: In Excel, there is an option called watch window which was introduced by Microsoft in 2013 version and it can be […]
  9. Data Validation with Date Range: You can use a Data Validation to restrict a user to enter a date out of a date range. For this all you need […]
  10. Consolidate Data From Multiple Worksheets: In Excel, there is a simple but powerful option which can help you combine data from multiple worksheets into a single worksheet […]

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube

8 thoughts

Leave a Comment

Your email address will not be published.

  1. I tried the dropdown list with the table method but was unable to add text to an entry (which is a capability I need), however, with the offset method I can overwrite, modify or add to the dropdown item chosen (in the text/fx box). Do you know of a way to do this with the table method, maybe with a wildcard? The dynamics of tables are very useful.

  2. Thanks Puneet for sharing such useful information. I normally use Dynamic Drop down list but the issue is if any cell of source sheet is protected then table do not update.
    I want to create a dynamic drop down list which can automatically sort alphabetically or have search option as the list I have to deal with have thousands of names in it.
    Please share