Excel Data Validation List from Table: Dynamically Update Your Drop-Down

Photo of author
Written By Chris

Just a simple "data guy". Changing the world one formula at a time.

You love using drop-down lists in Excel, right? Who doesn’t? They make your worksheets more functional, impressive, and simple to cut through all the data and find what you need. 

But when your data validation list is sourced from an ever-changing table, you really don’t want to update the range of your drop-down constantly. I know I don’t.

Fear not! There is a quick way to make your drop-down list as dynamic as your table.

The two best solutions to grab dynamic data from a table into your Excel data validation are to either use the INDIRECT function or add a named range meant to update as you add and remove rows in your table.

I’ll show you how easy this can be for Excel users of any skill level.

Using a Table Instead of A Range to Source Your Drop-Down

I have a range of data in my example, and I want to use the first column of position titles for my data validation.

Of course, you could create the drop-down from a simple range without converting it, but a more extended formula would be needed to make it dynamic. And it’s an inefficient way to complete the same task. Plus, working with tables is far less complicated!

So, first things first, select the range or press CTRL + A, then use the keyboard shortcut CTRL + T to convert it to a table. In my case, I have headers, so I’ll check the box to include them and press OK.

create table for data validation list in Excel

With the table inserted, I’ll go to my second tab, where I have the same three column headers. In the second and third columns are lookup formulas to pull the correct statistics based on the value in column A, which will be my drop-down.

I’ll select cell A2, and under the Data tab, find the Data Validation option. When the window appears, change the Allow drop-down to List, and for the source range, I’ll select the Positions from my table.

Hit OK, and test out the new drop-down. It works as expected, but if I add a new row at the bottom of my table, the latest entry doesn’t reflect in the data validation list.

added new row to table but not in list

Not surprising. So now, how do we fix this?

Method #1 – Use a Named Range for A Dynamic Drop-Down List

If you aren’t familiar with Named Ranges, they are defined sets of your data that you can reference just by the name you assign them. You can even include formulas into a Named Range to avoid a lengthy function in your formula bar.

To see or edit a Named Range, navigate to the Formula tab on the Ribbon and click the Name Manager button.

When the Name Manager opens, I already have one Named Range, referring to my table, including all columns and rows.

named manager selection from ribbon for single named range of table.

The benefit of converting our data to a table is that it is dynamic by default, so as we edit the values, the existing Named Range updates.

But now we’ll add a new range that works off the existing table range to pull the single column in all its dynamic glory.

Click the New button found in the window and name the range. In the “Refers to” field, I’ll type in the defined column of the table, which is represented as =Table1[Position].

adding a new named range for first column of table.

The brackets are key because they tell Excel that the value between them (the header) signifies a specific column. Click OK, and you now have a new Named Range for a single column in your table. More importantly, it updates dynamically!

Now, I’ll go back to the Data Validation list previously created and change the selection of absolute cells to the title of the newly created Named Range. You can do this by placing your cursor in the field, pressing F3 to bring up the available Named Ranges and paste it. 

press F3 to insert named range into data validation refer to field

After clicking OK, I return to my drop-down, and the new entry of “Consultant” is finally visible.

completed dynamic drop-down list with named ranges

If you delete any table rows or add new entries, this list will dynamically update without adjusting the validation.

Method #2 – Using the INDIRECT Function

You do have another option to ensure your data validation list remains dynamic along with your table, and that’s by using the INDIRECT function.

The INDIRECT function in Excel creates a reference from a text string. By incorporating it into the drop-down reference, you can do the same thing as method number one without having to use a second Named Range. Let me show you.

First, I’ll select the cell we want the drop-list to appear and then open the Data Validation window again. In the “Refers to” field, I’ll type =INDIRECT(“Table1[Position]”).

alternate method to use the indirect function in data validation.

You see, INDIRECT tells Excel to treat the text string as a defined range. Without it, you’d get an error message.

When you test out your INDIRECT drop-down validation list, you’ll see that it changes with any updates you make to your table.

It doesn’t matter which path you take, as both work great. As I said, there are other ways to get the same results, but these two methods are the simplest to perform, especially for beginner Excel users.

Leave a Comment