Learn how to create an Excel drop down list the right way. Yes, there is a wrong way and I will show you below.
The right way to create a drop down list is to make it dynamic so you can always add new items to your list without changing any formulas or data validation rules in order to incorporate the new values.
Static drop down lists – the wrong way
Let’s assume you have a list of cars in the first column and you want to create a drop-down list to be able to pull your favorite car from that list.
The first step would be to name your range. Let’s name the range CarsILike. Select the range containing car names, right click, click on Define Name and type in the name. Notice the range selected.
Next, you select the cell where you want your drop down to be and go to Data tab – Data Validation. Select List and in the formula section write =CarsILike. Click OK.
You now have a drop down list picking from the named range CarsILike
However, if you want to add new cars to the list, the name range does not extend to the new values. Your drop-down will still show the original values.
Dynamic drop-down – the right way
Using the same example, before naming the name range, let’s turn the list into a table. Now the table is dynamic and any value added down the list will be part of the table.
To turn the list into a table, select the list, go to the Insert Tab and click on Table.
Select “My table has headers so that Cars becomes the header
Once the data is organised as a table, click on the list of cars (just on the values) and go to the Formulas tab and click on Define Name
Now, let’s define the name range CarsILike as the values in the table. Now the name range is linked to a dynamic table and will take any new values as part of the named range CarsILike.
Notice the definition. It is now linked to the column named Cars in the table Table 1. It is now defined dynamically. That means that any value you add in the column Cars will be picked up by this named range.
You can add as may items you want to the table and they will show in the drop-down immediately because the values are picked from the dynamic range
