How to use Excel Table within a data validation list (3 ways)

Excel Tables expand automatically whenever new data is added to the bottom. This feature alone makes Tables one of the most powerful tools within the Excel users toolkit. A Table can be used as the source data for a chart and within a named range, both of which benefit from the auto-expand feature. Data validation lists would also benefit from the auto-expand feature… but it just doesn’t work; an oversight on Microsoft’s part (in my opinion).

The following data validation settings look like it should work. It should use the column called Animals from a Table called myList.

But Excel will show an error message instead.

What can you use to solve this problem? I’ve got three solutions for you:

• Normal cell references over a Table
• Named range of the Table column
• INDIRECT function

Let’s look at each of these and you can decide which is the best option for your situation.

Download the example file: Click the link below to download the example file used for this post:

Watch the video

Watch the video on YouTube.

Example Data

The examples in this post use the following data:

The Table name is myList (I know, it’s not a great name, but it will work for the examples).

Normal cell references over a Table

If we use standard cell references, the data validation list will be static. Each time a new item is added to the list either the data validation source will need to be updated, or the new items will need to be added within the existing list range. However, if we create the data validation list using normal cell references over a Table column, it will expand.

Look at the screenshot below. The cells $A$2:$A$7 are the same cells as the reference myList[Animals] would be for a Table.

It looks like it would behave like a standard static list. But somehow Excel knows, and the data validation list expands when values are added to the Table. Try it for yourself.

Problems with this approach

There are some big problems with this approach:

1. If the data validation list is on a separate sheet to the Table, it will not expand.
2. This method only works if the cells selected include the entire Table column (i.e., in our example, if you reference $A$4:$A$7 it will not expand when a new item is added as it is not the whole Table column).

Due to the issues noted above, it is probably best to avoid this method.

Named range of the Table column

Named ranges will accept a Table column as the source, and a data validation list will accept a Named range. Therefore, we can take a two-step approach to achieve the same result.

Create the named range

To create the named range, click Formulas -> Define Name

The New Name window will open. Give the named range a name (myDVList in the example below) and set the Refers to box to the name of the Table and column.

Finally, Click OK. The named range has now been created.

The formula used in the screenshot above is.

=myList[Animals]

If the list only has one column, it is possible to refer to just the Table without the column name (just like the screenshot below.

=myList

Add the named range to the data validation list

The screenshot below shows the named range added to the data validation list box.

The data validation list will now expand whenever new items are added to the Table.

Key points about this method

In relation to this method, there are a few things to be aware of:

• This is a two-stage process, which takes a little longer to set-up.
• If the Table or Column names change the named range automatically updates to reflect the changes.

While it takes longer to set-up, this method is the one that is least likely to break.

INDIRECT Function

The INDIRECT function will convert text into a Range which Excel will recognize and use in calculations. For example, if Cell A2 contains the text “A1” you can write =INDIRECT(A2) and Excel will convert that to the cell reference =A1.

We can use the INDIRECT function with the Table and column reference as text and Excel will understand this.

The formula used in the screenshot above is.

=INDIRECT("myList[Animals]")

If the list only has one column, it is possible to refer to just the Table.

=INDIRECT("myList")

The data validation list will now expand whenever new items are added to the Table.

Warning about this method

There is one key thing to be aware of; the Table and column names are now hardcoded into the INDIRECT formula. If either of these change the data validation list will cease to work as the formula cannot find the source data.

Which method to choose?

So, with three options to choose from, the question is which is the best. Personally, I prefer the named range option. I use named ranges often, so it fits with my way of working. However, if I have to create a lot of named ranges, I may be lazy and choose the INDIRECT method. I don’t advise using the standard cell references over a table as it won’t work across worksheets.