This article will show you how to automatically update your dropdown list
Table of Contents
Create a dropdown list in Excel
To create a dropdown list activate the menu Data > Data Validation
- Select a cell (in our example F1)
- Select the menu Data > Data Validation
- In the dialog box, select the option List
- Then select the cells that contain your data
Now your cell (F1) displays a small arrow. Click on it to display the list of the data selected.
Note: If you add a new values in your source, it will not added in the data validation.
Create a dynamic dropdown list
To make a dropdown list dynamic, use the Table tool.
- Select your data source
- Activate the tool Insert > Table
- Very important: Check the option My table has headers
- Re-open the tool Data > Data validation
- Change the source with the following formula:
And now your data source is dynamic 😍😍😍
Explanation of the formula
Firstly, it is important to know the name of your Table. By default, Excel names Tables with the following names: Table1, Table2, .... But if there are already Tables in your worksheet they may have different names.
To find the Table name :
- Select one of the cells in the Table
- Go to the tab Design (last tab to the right)
- The name of the Table is displayed on the top of the ribbon
Write the reference of the column Name inside the Table like this:
But this writing doesn't work 😡😤💥. However, if you insert this reference in the INDIRECT function, it works 😍😃👍