QuickBytes is a series of articles for simple and easy to implement techniques with exponential benefits
Creating a dynamic drop-down list in Excel is quite a simple process. We use the Excel table feature along with the Name Manager to achieve this.
Also see: Benefits of Excel Table
Step 1: Create a table with the drop-down list
For this purpose, we require to create a dropdown for country selection in the cell M7.
Create a single column table with countries.
Optional: Assign appropriate table name (in this example, tblCountry)
Step 2: Add a Name Range
Formula > Name Manager (Keyboard Shortcut – CTRL+F3) > New
Assign an appropriate name and select the column (minus header) under Refers to:
It should read as tblName[ColumnName].
Step 3: Use the defined name range in the Data Validation
Select the cell (M7) > Data > Data Validation > Allow > List
Source: Add the name range created in the previous step
Tip: Use F3 to call Paste Name window for adding the name range to Source.