A common problem when you update your source

1. You have created a dropdown list in F1 links to the range D2:D5

2. Then you add a new name in D6.
3. Unfortunately, the name doesn't appear in the dropdown list

The reason is simple.

• The range of cells of your dropdown list is still D2:D5 (and it should be D2:D6).

• To achieve a dynamic dropdown list, utilizing a Table is essential.
• Tables provide dynamic ranges for data, ensuring flexibility and efficiency in dropdown list creation.

Create a dropdown list with Table References

Now, you insert your data into a Table

1. Select your data source
2. Activate the tool Insert > Table

The result is this

Now, you want to use the Table's references as the source of your dropdown list. But there is a problem 😱😱😱

Excel doesn't accept Tables' references as a source in any dialog box, such as data validation, custom conditional formatting rule, etc.

And you have this message

But there is a trick to avoid this problem

The trick is to write your Table's references in the INDIRECT function 😉

1. Write the function name =INDIRECT(
2. Add a double quote (")
3. Then, the Table's references
4. Another time, add a double quote to close the string.
5. Close the parentheses

=INDIRECT("Table1[Name]")

And now your data source is dynamic 😍😍😍

"Invisible" Dynamic reference

Here, it's tricky. Because

1. Our data for the dropdown list is still inside a Table
2. Open the dialog box of the dropdown list
3. Select the source field
4. Click on the Table column's header to select this column's whole data.
5. The references look statics, but that's a trap !!!!!!!

Now, when I add a new name in D6, what do you think will be going on with the dropdown list?

1. I will have only 4 names?
2. Or will the new one be added to the dropdown list?

The answer is 2.

The name will be added to the dropdown list because I have selected my data from the table header. Excel knows the source is inside a Table even if It didn't write the source as a Table's reference. 😉👍

When you check the source field of the dropdown list, you can see that the reference has been updated accordingly.