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

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.

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.

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

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

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.

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.

Step 2:

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

Step 3:

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

Step 4:

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

Step 5:

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

Read More: Excel Data Validation Drop-Down List

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.

Step 2:

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

Step 3:

• Then, click on the New.

Step 4:

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

Step 5:

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

Step 6:

• Finally, press Enter to see the list.

Step 7:

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

Step 8:

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

Read More: Excel Data Validation Drop Down List with Filter

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]’.

Step 2:

• Then, type the following formula with the INDIRECT function:

Step 3:

• Finally, press Enter to see the list.

Step 4:

• Insert a text at the bottom of the table.

Step 5:

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

Download Practice Workbook

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

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.

Related Articles

• How to Use Data Validation List from Another Sheet
• How to Set Limit in Excel Cell
• How to Use Excel Formula Not to Exceed a Certain Value

<< Go Back to Data Validation in Excel | Learn Excel