steps to create dynamic drop down list in excelBy 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.

different-between-dynamic-drop-down-list-in-excel-and-normal-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.

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

create a dynamic range to create dynamic drop down list in excel

  • 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.

click on data validation to create dynamic drop down list in excel

  • In setting tab, select the list option from “Allow”.
  • And, in source input enter named range.
  • Click Ok

click ok to create dynamic drop down list for excel 2007 above

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

Quick Tip: If you have your source list and drop down list in the same worksheet, there is no need to create a named range.

Simply refer to your table while creating a drop down list, it will automatically update drop down when you add a new entry in 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”.

=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 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.

Last Words

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.



  • 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!