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

Excel Tables expand automatically whenever new data is added. This feature alone makes Tables one of the most powerful tools within the Excel user’s 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 they just don’t work correctly—an oversight on Microsoft’s part (in my opinion).

In this post, we examine the issues and solutions for creating an Excel data validation list from a Table.

Table of Contents

Download the example file: Join the free Insiders Program and gain access to the example file used for this post.

File name: 0076 Data Validation with Tables.zip

Example data

The examples in this post use the following data:

Source Data for Data Validation List

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

The problem

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

Data Validation with Structured Reference

Instead of giving us an Excel data validation list from the Table, Excel shows an error message.

Data Validation - Error Message

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

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

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

Normal cell references over a Table

Normally, if we use standard cell references, the data validation list is 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, which also happen to be all the rows from a Table column, it will expand.

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

Standard Cell references for Data Validation

It looks like it would behave like a standard static list. But somehow Excel knows.

If we add data to the bottom of the Table, the data validation list expands when values are added to the Table. Try it for yourself.

Problems with this approach

Now, don’t get too excited. There is a big problem with this approach:

  1. If the data validation list is on a separate sheet to the Table, it does 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.

INDIRECT function

The INDIRECT function converts text into a Range that Excel recognizes and can use in calculations. For example, if Cell A2 contains the text “A1” you can write =INDIRECT(A2) and Excel converts 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.

INDIRECT method for creating a Data Validation list from a Table

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 range for the data validation list now expands whenever new items are added to the Table.

Warning about this method

With this method, there is one key thing to be aware of; the Table and column names are 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.

PRO TIP:

INDIRECT is normally considered a volatile function, which, in some circumstances, can cause slow calculations in Excel workbooks.

However, data validation lists are only recalculated when we interact with the cell. Therefore, INDIRECT does not perform a volatile function in this context.

Named range of the Table column

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

Create the named range

To create the named range, click Formulas > Define Name

The New Name dialog box opens.

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.

Click OK. The named range has been created.

Create Named Range for Data Validation With Structured Reference

The formula used in the screenshot above is.

=myList[Animals]

As with the example above, if the Table only has one column, we can refer to just the Table without the column name.

Add the named range to the data validation list

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

Data Validation with Named Range

The data validation list now expands 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 harder to break.

Dynamic array spill range

We are about to get technical for a moment, but don’t worry applying this method is very easy, so stick with me.

Excel 2021 and Excel 365 have a feature known as dynamic arrays. These allow a formula to return multiple results and to spill the results across multiple cells; this is known as a spill range.

If the number of rows returned by the formula changes, the rows and columns in the spill range also change.

We use the # sign (also known as the spill operator) to refer to the cells in the spill range.

Here is the key part… data validation lists accept the spill range as a valid range reference. So this means we can create a two-step process:

  • Create a spill range
  • Use the spill range in a data validation list

Create a spill range

To create the spill range on a sheet, we just need to refer to the Table and column.

Spill Range

The formula in cell F2 is:

=myList[Animals]

Since the Table column has multiple cells, those values spill down into cells F2:F7. This can be seen by the blue box which highlights the spill range.

If the number of items in the Table changes, the spill range of the Table changes.

Just as for previous methods, if the Table only contains a single column, we could refer to just the Table name of myList, instead of the myList[Animals].

Use the spill range in the data validation list

To get the spill range into the data validation list, we refer to the first cell in the range, followed by #.

Data Validation list with spill range from Excel Table

In the screenshot above, we are using cell F2 and its spill range as the source for the data validation list.

If the data validation list refers to the range on another worksheet, the sheet name must also be included in the source, such as:

='Sheet Name'!$F$2#

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 formulas update automatically to reflect the changes.

Which method to choose?

So, with four options to choose from, the question is which is the best?

I don’t advise using the standard cell references over a table as it won’t work across worksheets. And I avoid the INDIRECT method as it can easily break if renaming Tables or columns.

I like the named range and spill range options. While they take a few extra seconds, they are easy to set up and less likely to break if changes occur.

Conclusion

We cannot create an Excel data validation list directly from a Table. However, in this post, we have seen four methods that we can use to achieve the same result.

Related Posts:


Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

Leave a Comment