Excel QuickBytes# 7: Dynamic Dropdown in 3 steps

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.




@imvivran

Drop Me a Line, Let Me Know What You Think

 

contact@vivran.in

+91 9871-641-146

Join WhatsApp group: BI Simplified

MS EXCEL || POWER BI || POWER APPS || POWER QUERY || TABLEAU || DATA ANALYTICS || TRAINING