How to Add a Drop Down List in Excel Using Tables

Creating drop down lists in Excel is a fantastic way to ensure data consistency and streamline data entry. While there are several methods to achieve this, using Excel tables combined with the INDIRECT function offers a dynamic and efficient approach, especially when dealing with lists that may change over time. This method avoids the pitfalls of static named ranges, which can become cumbersome to manage and update.

This guide will walk you through how to set up a dynamic drop down list in Excel using tables and the INDIRECT function. This technique ensures your drop down lists are always up-to-date and reflect the current data in your lookup tables.

Step-by-Step Guide: Creating a Dynamic Drop Down List with Tables and INDIRECT

Let’s break down the process into manageable steps:

1. Create Your Lookup Table

First, you need to create a table that will serve as the source for your drop down list. This table should contain the values you want to appear in your drop down.

In a new Excel sheet, enter your list of values in a column. For example, you might create a list of “Yes” and “No” options.
Select the range containing your list (including the header if you have one).
Go to the Insert tab on the Excel ribbon and click on Table.
In the “Create Table” dialog box, ensure that “My table has headers” is checked if your selected range includes a header row, and click OK. Excel will now format your range as a table.

Alt text: Excel sheet showing a table named ‘TYesOrNo’ with columns ‘ID’ and ‘Code’, used as the source for a dropdown list.

2. Name Your Table (Optional but Recommended)

While Excel automatically names tables (like Table1, Table2, etc.), giving your table a descriptive name makes your formula easier to understand and maintain.

Select any cell within your table.
Go to the Table Design tab on the Excel ribbon (it appears when you select a cell in the table).
In the Properties group, locate the Table Name box.
Enter a meaningful name for your table, for example, TYesOrNo (as used in the original article example) or ValidationList. Press Enter.

3. Set Up Data Validation with the INDIRECT Formula

Now, you’ll set up the data validation for the cells where you want the drop down list to appear.

Select the cell(s) where you want to add the drop down list.
Go to the Data tab on the Excel ribbon and click on Data Validation.
In the “Data Validation” dialog box, under the Settings tab:
- In the “Allow” dropdown, select List.
- In the “Source” box, enter the following formula, adjusting TYesOrNo to your table name and ID to the name of the column containing the values you want in the drop down:
```
=INDIRECT("TYesOrNo[ID]")
```
Click OK.

You should now see a drop down arrow in the selected cell(s). Clicking on it will display the list of values from the specified column of your table.

4. Understanding the INDIRECT Formula

The magic behind this dynamic drop down list lies in the INDIRECT function. INDIRECT converts a text string into a valid range reference.

In our formula =INDIRECT("TYesOrNo[ID]"):

"TYesOrNo[ID]" is a text string that refers to a structured table reference.
TYesOrNo is the name of the table we created.
[ID] is the name of the column within the TYesOrNo table that contains the drop down values. Table names and column names within table formulas are enclosed in square brackets.

Excel interprets this text string as a reference to the entire ‘ID’ column of the ‘TYesOrNo’ table, making the drop down list dynamically linked to the table’s contents.

5. Benefits of Using Standard Column Names and Tables

Using tables and the INDIRECT function offers several advantages:

Dynamic Lists: When you add or remove items from your lookup table, the drop down lists automatically update. No need to redefine named ranges.
Maintainability: Using table and column names makes your data validation source easier to understand and modify compared to cell ranges or named ranges.
Reusability: If you consistently use standard column names like “ID” or “Code” in your lookup tables, you can easily reuse the data validation formula by only changing the table name. This simplifies creating multiple drop down lists across your worksheets.

Advanced Tips for Dynamic Drop Down Lists

Sorting Your Drop Down List: If you need your drop down list to appear in a specific order, you can add a “Sort” column to your lookup table and sort the table based on this column. The drop down list will reflect the sorted order of your table.
Creating Unique and Informative Drop Down Values: You can use calculated columns within your lookup table to create more descriptive drop down values. For example, you could combine two columns to display both a code and a description in your drop down list, enhancing clarity for users.

Conclusion

Using Excel tables and the INDIRECT function provides a robust and dynamic way to create drop down lists. This method is superior to using static named ranges because it automatically adjusts to changes in your data, making your spreadsheets more efficient and easier to maintain. By following these steps, you can create user-friendly and dynamic drop down lists that enhance data entry and consistency in your Excel workbooks.