A data validation list is an excellent feature that helps you quickly enter correct information into a data range. Let’s say you are entering departments, wouldn’t you rather select the names from a drop down list instead of typing? A dynamic data validation list is a better option.
With a dynamic list as you edit/update the source data, the validation list will display the new data. For example, if you add more departments to your source data, the validation list will reflect the current data. Therefore you don’t have to edit the data validation list. To create a dynamic data validation list we shall use tables, named ranges and data validation.
Note: Download the workbook to practice this exercise
Applies To: Microsoft® Excel® 2010 and 2013
- We start by creating a table.
a. Select the data range K1:K5.
b. Select the Insert tab.
c. Select Table
d. Click OK and ensure my data has headers option is ticked.
- Then we create a defined name.
a. Select the data range K2:K5.
b. Place the cursor in the name box.
c. Type Dept and press enter.
- To create a dynamic data validation list.
a. Select the data range C2:C11.
b. Select the Data tab, then Data Validation under the Data Tools group.
c. Select List under the Settings tab.
d. Under Source, press F3 and select Dept.
e. Select OK.
f. A dynamic data validation list will be created.
g. Place the cursor in cell K6 and type Marketing.
h. Press Enter and type Development.
i. Press Enter.
j. Click cell C2 and select the drop down arrow. The new departments have been added to the list.
You can now quickly select the appropriate departments from a drop down list instead of typing them out each time.
