Home > Blog

How to make a dynamic drop-down box

In the blog (Excel – How To Make A Drop-Down Dependent On Another Drop-Down), I showed how to make the contents of a drop-down box dependent on the contents of another cell or drop-down box.   To do this, I used named ranges and the INDIRECT method.

If we use tables, the named range becomes dynamic – thus, if you add additional items to the bottom of the list, the named range automatically expands to include them.

To set up the table, select the data (including the title row) and press <CTRL><L>.  This displays the table definition dialogue


how to setup a table in Excel

Check that the data selected is correct and tick the “my table has headers” box.

To name the range, copy the text that you used for the table header (which needs to have no spaces) and paste it into the table name box under the Table Design menu

how to name a table in excel to make a dynamic data range

Now your named range is dynamic and will increase when you add more items to the list.

Miles Goodchild

Miles Goodchild has been a Program and Programme Planner, PMO lead and Project Manger since 1998.  He enjoys using MS Office, especially Project, Excel and Visio to make life easier and simpler.  In the course of this he created SummaryPro.  In this blog he shares some of the tips and techniques he has learnt over the years in the hope that they will be useful to you.