Excel Productivity Guide

Get instant access to this free e-book fully loaded with Useful Excel Tips, 80+ Keyboard Shortcuts, and VBA Codes.

Dynamic Drop Down List

    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.

    how to create a dynamic drop down list in excel

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

    For this, the best solution is to use a dynamic drop down list in which you don’t have 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.

    When you added a new entry into the source list, that entry is automatically updated in dynamic 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.

    So today, in this post, you will learn two different methods to create a drop down list with a dynamic source range.

    Methods To Create a Dynamic Drop Down List

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

    1. Excel Table
    2. Offset Function in Named Range

    Instant Access: Download this quick PDF guide to learn more about a dynamic drop down list.

    Using a Excel Table

    Using a table to create a dynamic drop down is super easy. We are using the list below to create a drop down list.

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

    Here are the steps.

    apply table to create dynamic drop down list in excel

    • Now, next step is to refer to the table range in the drop down list data source. Well, if you try to directly refer to the table range it will return an error.

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

    • For this, you need to use the INDIRECT function for referring to the table range.

    =INDIRECT(“Table5[Month]”)

    use indiect function to refer to the table range to create a dynamic drop down list

    • Click OK.

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

    Using 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 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”.

    =OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!$B$3:$B$1048576),1)

    create a dynamic range to create drop down list for excel 2003

    • Click Ok.
    • Now, create a drop down list by using the dynamic range you have created.

    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 sample file to learn more about this tips

    Conclusion

    Using a dynamic drop down list is a big 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.

    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.

    More on Drop Down List



    • Armen Petrosyan

      INDEX function also can be used for dynamic range name.

      • Puneet Gogia

        Armen, you are awesome. Thanks for sharing.

    • Very good post!

      For those interested in learning more about drop down lists, check out this post:
      http://www.masterdataanalysis.com/ms-excel/drop-down-lists-in-ms-excel/

      It shows how to create dependent lists, how to display a tooltip when the list is selected, and many more tips.

      • Puneet Gogia

        Thnaks for sharing. Orlando

        • Armen Petrosyan

          You too!