How to Make a Data Validation List from Table in Excel (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

When new elements are inserted into the bottom of an Excel table, it extends dynamically. Tables are one of the most effective features in an Excel user’s toolbox simply because of this capability. A data Validation list is used to keep the Table data out of Error. But we need to update the Data Validation list while adding new data to the Table. In this tutorial, we will show you, how to make a dynamic Excel Data Validation list from the table.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Effective Ways to Make a Data Validation List from Table in Excel

In the image below, there is a sample data set to apply the validation list.

Effective Ways to Make a Data Validation List from Table in Excel

To do this, generally, we will open the Data Validation option from the Data tab.

Effective Ways to Make a Data Validation List from Table in Excel

Then, we will select the List option as Allow and type the table name with the header (Table179[States]).

Effective Ways to Make a Data Validation List from Table in Excel

But it won’t work. It will show this message box as shown in the image below. We will use three approaches to fix the problem. First, we’ll apply cell references, then a named range, and lastly, the INDIRECT function will be assigned to the Data Validation list.

Effective Ways to Make a Data Validation List from Table in Excel

1. Apply Cell References in Data Validation List from Table in Excel

For applying direct cell references in the Data Validation list, follow the outlined steps below.

Step 1:

  • Go to the Data tab and select the Data Validation.
  • Select the List in the Allow.

Effective Ways to Make a Data Validation List from Table in Excel

Step 2:

  • In the Source box, select the range B5:B11 without the header in the Table.
  • Finally, press Enter.

Effective Ways to Make a Data Validation List from Table in Excel

Step 3:

  • Therefore, your Data Validation drop-down list will appear.

Effective Ways to Make a Data Validation List from Table in Excel

Step 4:

  • Now, add type an extra element ‘Texas’ at the bottom of the table.

Effective Ways to Make a Data Validation List from Table in Excel

Step 5:

  • As a result, the ‘Texas’ is added to the Data Validation

Effective Ways to Make a Data Validation List from Table in Excel

Read More: Create Data Validation Drop-Down List with Multiple Selection in Excel


2. Use a Named Range in Data Validation List from Table in Excel

You can apply a name to the range in Table. To create a Data Validation list by naming the Table, follow the instructions below.

Step 1:

  • Select the cells in the range without the Table Header.

Effective Ways to Make a Data Validation List from Table in Excel

Step 2:

  • Then, click on the Formulas Tab.
  • Click on the Name Manager.

Effective Ways to Make a Data Validation List from Table in Excel

Step 3:

  • Then, click on the New.

Effective Ways to Make a Data Validation List from Table in Excel

Step 4:

  • Type any name you want to introduce, we have typed ‘Named_Range’.
  • Press Enter.

Effective Ways to Make a Data Validation List from Table in Excel

Step 5:

  • In the Data Validation Source box, type the following name.
=Named_Range

Effective Ways to Make a Data Validation List from Table in Excel

Step 6:

  • Finally, press Enter to see the list.

Effective Ways to Make a Data Validation List from Table in Excel

Step 7:

  • In the bottom cell of the table, type ‘Texas’.

Sample Data

Step 8:

  • Therefore, the ‘Texas’ option will be added to the drop-down option.

Sample Data

Read More: How to Use Named Range for Data Validation List with VBA in Excel


Similar Readings


3. Insert the INDIRECT Function in Data Validation List

Moreover, we can use functions in the Data Validation box. We will apply the INDIRECT function in the Data Validation Source box. The INDIRECT function is used to find the range of a certain text. It returns the range under a certain cell value. To apply the function, follow the steps below.

Step 1:

  • In any cell, type ‘=’ equal to sign and select the range.
  • Copy the range name ‘Table18[States]’.

Sample Data

Step 2:

  • Then, type the following formula with the INDIRECT
=INDIRECT("Table18[States]")

Sample Data

Step 3:

  • Finally, press Enter to see the list.

Sample Data

Step 4:

  • Insert a text at the bottom of the table.

Sample Data

Step 5:

  • Therefore, it will be added to the Data Validation list automatically.

Sample Data

Read More: Excel VBA to Create Data Validation List from Array


Conclusion

Finally, I hope you have a better understanding of how to create an Excel Data Validation List from a Table. All of these strategies should be implemented while educating and practicing with your data. Examine the practice book and apply what you’ve learned. We are inspired to continue delivering programs like this because of your valued support.

If you have any questions, please do not hesitate to contact us. Please share your thoughts in the comments section below.

The Exceldemy staff will get back to you as soon as possible.

Stay with us and continue to learn.


Related Articles

Bhubon Costa
Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo